color a range of cells based on a criteria

[Dave F]

Hi,

I don't believe the conditional formatting tool gives me a solution to this
problem, so I probably need VBA.

Range B2:M2 contains the months January through December. January is in B2
and December is in M2.

In the range B1:M1, "business as usual starts" will either appear or not.
If it appears, its location will vary across that range.

I'm looking for VBA that will do the following:

1) If "business as usual" is in C1, the range B3:B14 will be highlighted
yellow, and the range C3:M14 will be highlighted green (remember, "business
as usual" will not necessarily be in C1, but can vary across the range
specified above)

2) If "business as usual" does not appear in the range B1:M1, then the range
B3:M14 will be highlighted yellow.

Clear?

I hope so.

Thanks in advance.

Dave

 

[Bob Phillips]

C F should do that

Select B3:B14 and use a formula of

=$C$1="Business as usual"

for red, <> for yellow

and

=ISNUMBER(MATCH("Business as usual"),$B$1:$M$1,0)

for the secondary yellow

You will need to determine priority.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave F]

Interesting suggestion. I'll try it, thanks.