# CONDITIONAL FORMATTING IN EXCEL - off of a range of cells

Solved/Closed
Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013 - Jul 29, 2013 at 10:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 30, 2013 at 10:24 AM
Hi
I've never used these forums before but this issue has been bugging me for some time now.... so i thought i would leave it to the experts....

I have one cell that i want to apply formatting on based off of the condition that another cell has some text in it.

Eg: Shade A1 BLUE IF B1 has the text "TRUE" in it

So in Conditional formatting I just have =B1 = "TRUE"

This works fine.

What if I want A1 to turn Blue if other cells are TRUE in column B? So it could be a whole range....

e.g

B1 = FALSE
B2 = FALSE
B3 = TRUE
B4 = FALSE etc.....

Within cell A1 I opened conditional formatting and I tried

=(B1:B200) = "TRUE"

But this did not work?

I also tried =RANGE(B1:B200) = "TRUE"

But this did not work either?

Any ideas?
Related:

## 4 responses

Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013
Jul 30, 2013 at 04:26 AM
Hi Trowa (anyone)

I have tested your solution and this is not working? Once I insert the condition the target cell A1 remains TRUE or shaded Blue for any value chosen in column B?

Please note that I have a drop down box in Column B with a number of outcomes but that shouldn't affect the formula? I even changed all the outcomes in the list so that none contain the word TRUE and still A1 remained Blue?

The logic seems sound but not sure why its not working?

Any other ideas?
Mounta1n Posts 8 Registration date Monday July 29, 2013 Status Member Last seen August 5, 2013
Jul 30, 2013 at 06:07 AM
Problem solved....its a COUNTIF function not a COUNTA!

=IF(COUNTIF(\$Q\$4:\$Q\$182,"No - differences unexplained above materiality")>0,TRUE,)

But like the logic!

Thanks
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 30, 2013 at 10:24 AM
Hi Brendon,

Strange, the formula worked for me. Maybe a translation issue (I'm using a function translation site Dutch --> English), but good to see you worked it out.

Best regards,
Trowa
sivri526 Posts 1 Registration date Tuesday July 30, 2013 Status Member Last seen July 30, 2013
Jul 30, 2013 at 07:50 AM
You select A1:A200 then new rule from validation data
=B1:B200=TRUE
Not: you dontrite appostrove "TRUE"
i exampled its solved
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 29, 2013 at 10:55 AM
Hi Mounta1n,

Try this:
=IF(COUNTA(B1:B200="TRUE")>0,TRUE,)

Best regards,
Trowa
Trowa

I have to say your response made me smile... Almost laugh. Very smart! Can't believe I didn't think of that. I will test it tomorrow at work but seems like a very sensible and clever option. Experts indeed.

I'll let you know.
Thanks
Brendon