tell if a cell is shaded

  • Thread starter Thread starter pcor
  • Start date Start date
P

pcor

I want to place the word "YES" in cell F2 if the cell A2 background is gray.
How do I do that Thanks
 
You'd have to do this with some macro code. Excel simply was never created
to make decisions based on cell shading or border color or style or anything
like that; it makes decisions based on the value/content of cells.

Now, if A2 is formatted gray via Conditional Formatting, we could probably
figure out how to set up F2 to display "YES" at the same time that A2 gets
turned gray. The question would be what is the condition that causes A2 to
turn gray.
 
Hi,
I manage to do this having downloaded the free "morefunc" add-in.
First you need to find the number corresponding to your fill colour, so fill
a cell (say A1) with a grey colour (middle one one the right in my pallette),
and in A2 enter =XLM.GET.CELL(63,A1)
This will return a number, 48, in this case.
Now you know the number, you can get rid of the above.

Now, in F2, enter
=IF(XLM.GET.CELL(63,A2)=48,"YES","")

If A2 has a midgrey fill, then F2 will display the word YES, otherwise it
will display nothing.

The drawback is that it doesn't update automatically, so you need to force a
recalculation (with F9, for instance), and also it won't work if the fill is
applied with conditional formatting. In that case, you need to apply the same
logic to a formula in F2 that triggered the conditional formatting in A2.

Dave
 
Back
Top