How to flag a cell if it contains any highlighting

[Dave K]

Hello - i am trying to generate an if statement that will produce the
word "yes" if the cell to the left of it contains any highlighting at
all. 99% of the cells that i am trying to flag contain yellow
highlighting, fyi.

So, for example, the formula in I2 I am trying to write will say, "if
h2 contains yellow highlighting" then display "yes". If not, display
nothing.

Thanks for any suggestions, or workarounds that may produce the same
outcome.

 

[Gord Dibben]

How did the cells become highlighted in yellow?

Manually or by Conditional formatting?

If by CF, construct formulas with the same criteria.

If manually, you will need VBA.

Do you need to go that route?

See Chip Pearson's site for CellColorIndex code.

http://www.cpearson.com/excel/colors.aspx


Gord Dibben MS Excel MVP

 

[Dave K]

Hello - i am trying to generate an if statement that will produce the
word "yes" if the cell to the left of it contains any highlighting at
all.  99% of the cells that i am trying to flag contain yellow
highlighting, fyi.

So, for example, the formula in I2 I am trying to write will say, "if
h2 contains yellow highlighting" then display "yes". If not, display
nothing.

Thanks for any suggestions, or workarounds that may produce the same
outcome.

Hi Gord. Thanks for the response. Yes, they were highlighted
manually.

Is there some simple VBA that would give me the color of the cells in
column H (and display the results in column I)....or something to that
effect?

 

[Zaidy036]

Hi Gord. Thanks for the response. Yes, they were highlighted
manually.

Is there some simple VBA that would give me the color of the cells in
column H (and display the results in column I)....or something to that
effect?

I think that you can use a simple IF by looking at Cell Color NOT Equal
to White (-4142)

 

[Dave K]

I think that you can use a simple IF by looking at Cell Color NOT Equal
to White (-4142)

Is there such a formula? If so, what would the formula be? Thx.

 

[Zaidy036]

Is there such a formula? If so, what would the formula be? Thx.

I suggest that you download and install the free ASAP Utilities.

It has a function ASAPCELLCCOLORINDEX whch can be used in an IF
White or colorless is -4142
=IF (ASAPCELLCCOLORINDEX(xx) = -4142, "","Yes")
where xx is cell you are testing

 

[Gord Dibben]

Did you go to Chip's site and find the Function CellColorIndex?

You copy and place that code into a module in your workbook then in I2 you enter

=IF(CellColorIndex(H2)=6,"yes","no")

Assuming that the shade of yellow used is index 6


Gord

 

[Dave K]

Did you go to Chip's site and find the Function CellColorIndex?

You copy and place that code into a module in your workbook then in I2 you enter

=IF(CellColorIndex(H2)=6,"yes","no")

Assuming that the shade of yellow used is index 6

Gord

Yes....got it. Thanks Gord. Was having trouble opening the .BAS file.
Then realized the function was in the webpage as well.

Thanks again!