Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

CONDITIONAL FORMATTING IN EXCEL - off of a range of cells [Solved/Closed]

Report
Posts
8
Registration date
Monday July 29, 2013
Status
Member
Last seen
August 5, 2013
 -
Posts
2629
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 25, 2020
 -
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?
See more

4 replies

Answer 1 / 4
Posts
8
Registration date
Monday July 29, 2013
Status
Member
Last seen
August 5, 2013
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?
Answer 2 / 4
Posts
8
Registration date
Monday July 29, 2013
Status
Member
Last seen
August 5, 2013
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
Report
Posts
2629
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 25, 2020
 426
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
Answer 3 / 4
Posts
1
Registration date
Tuesday July 30, 2013
Status
Member
Last seen
July 30, 2013
You select A1:A200 then new rule from validation data
=B1:B200=TRUE
Not: you dontrite appostrove "TRUE"
i exampled its solved
Answer 4 / 4
Posts
2629
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 25, 2020
 426
Hi Mounta1n,

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

Best regards,
Trowa
Report

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
Ask a question