Find columns with certain text then conditionally format the contents

[Andula]

I'm trying to find a way to find all columns within a worksheet that have RH Bot in them (subheading, always in cell 2), then activate the columns and conditionally fomat the cells below (range 3 to 500) to be dark green, bold text on pale green background if the figures in the cells are greater than or equal to 50. Also bold red text on a pink background if they are less than 50.

The only solution I've found is to record a macro of scrolling through the sheet highlighting the columns myself and then applying the formatting, which takes ages when I run it.

Any help would be greatly appreciated.

[brokenbiscuits]

Highlight rows 3:500 and add the following conditional formatting formula:

=AND(COUNTIF(A$2:A$2,"*RH bot*")>0,A3=>50)
(for values 50+)

=AND(COUNTIF(A$2:A$2,"*RH bot*")>0,A3<50)
(for values -50)

[Andula]

Thanks for having a look at this for me. I've tried entering that formula as a 'new formatting rule' under the option 'use formula to determine which cells to format' but it tells me tehre is an error in the formula.

THe text RH Bot is always in the 2nd cell down, i.e. C2, D2 etc., could it be the A$2:A$2 part thats wrong?

[brokenbiscuits]

No, the A$2 bit is right, so long as the area you're highlighting begins with column A - it will change depending upon the cell, but the anchor ($) next to the 2 means the 2 will never change.

Even if it was wrong, it wouldn't tell you there was an error, it just simply wouldn't work. Are you typing it exactly as shown above? It seems to work for me.

EDIT: Just realised the reason the top one isn't working - you need to use >= rather than =>!

[Andula]

Great, that works, thank you. The only thing is, in the example I'm working on, there are 29 rows and the format doesn't work in the last 2 rows. But it does apply it to the top 2, which I don't want.

EDIT: It's ok, I've sorted it - thanks again