Condition based on color (1 Viewer)

[Dhruva]

have one column that consists of many rows. Some rows are highlighted in Yellow and some are not. I want to add another column to this spread sheet. If the cell is highlighted in yellow it should auto populate with "X" else with "y" in the new column.

Ex: INput

LCD366
LCD378 (this cell is highlighted in yellow)

Expectation:


LCD366 --- Y
LCD378 --- X

Basically, I need a formula based on the color of the preceding cell.

Thank you...

 

[shades]

Can't do it with a formula. You will have to use VBA, in one of two ways. 1) write a UDF - User Defined Function in VBA, then use it like you would a regular function; or 2) write code that automatically does it, and could be called by a button when needed.
________
Grand vitara

 

[Dhruva]

Can't do it with a formula. You will have to use VBA, in one of two ways. 1) write a UDF - User Defined Function in VBA, then use it like you would a regular function; or 2) write code that automatically does it, and could be called by a button when needed.

Thank you shades for this piece of educational information.

 

[shades]

Howdy. There is an Excel add-in that would give this capability: ASAP Utilities. There is a new function (UDF approach above) that can give the color index of the cell.

=ASAPCellColorIndex(A2)

Then you could combine this with an IF statement: (Yellow is Color Index 6 in standard Excel palette - if you change the palette, then the Color Index number will change)

=IF(ASAPCellColorIndex(A2)=6,"X","Y")

And copy down.

The key, however, is that you have to force a recalculation of the workbook (CTRL + ALT + F9) after changing the colors in the cells.
________
Michigan Dispensaries

 

[Brianwarnock]

Hi Shades that addin is an interesting bit of info, can i warn here that the original colour index is not changed by conditional formatting ie if the spreadsheet was created with no colour the CI would be -4142, if the cell is chaged to yellow by conditional formatting the CI is still -4142, why it works like this I don't know but it does, or did last time I checked.

Brian

 

[shades]

You are right about conditional formatting. However, if you physically set the color to Yellow after conditional formatting, then hit CTRL + SHIFT + F9, it will force a recalc, and the formula will then work.

BTW, that is why in many cases I set "conditional" formatting with code, because it doesn't have the limitations of conditional formatting (not just number of case, but over-ride, etc.)
________
LAMBORGHINI MARZAL SPECIFICATIONS

 

[MGumbrell]

This is a very helpfull thread, but can anyone tell me where I can locate the colour code's ie 6=yellow. Where can I find a list of the other codes?

Regards, Matt

I have tried putting the following into Conditional Formating
=ASAPCellColorIndex(A2)=6
and then set the condition from it, I am however receiving an error as it will not accept this.

Can you plese advise what I should be doing

Regards, Matt

 

[Brianwarnock]

Hi
I have not used the ASAP utility mentioned by shades however I don't thing you put the code in conditional formatting but in the cell where you want the result, see Shades post#4

As to the InteriorColorIndex I don't know where they are listed, but the following code can list it for you. It puts it in the adjacent cell, just highlight the colors and run the macro.

Sub ListColorIndex()
    
For Each c In ActiveWindow.RangeSelection
           c.Offset(0, 1).Value = c.Interior.ColorIndex
    Next c
    
End Sub

Brian

 

[MGumbrell]

Thank you

Regards, Matt