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...

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.

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.

Howdy. There is an Excel add-in that would give this capability: ASAP Utilities (http://www.asap-utilities.com/). 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.

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

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.)

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

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

Thank you

Regards, Matt