Best method for formula

[rjc4]

Hi All,
I wish to colour a cell in a range if the contents meet certain criteria. I can use conditional formatting to do this, but in this instance I wish to use a formula within a macro.
Example:
Range P5:P20
Color cell Green if the contents equal 6.

Can do this by formula =if(P5=6,then color it green) and copy it down to P20.
Is there a better way to define my formula using my range in the formula rather than copying it down.

Probably a dumb question but can anyone suggest a better way.
Thanks

 

[xld]

It's either conditional formatting or VBA event code.

 

[aroxo]

Do you mean you want to avoid duplicating the VB code and you are looking to associate the code to an event other than a change in the individual cell?

 

[rjc4]

I'm already using conditional formatting in my range to highlight numbers between two source numbers. However, I also wish to just similarly highlight another discreet number in the same range but outside my original numbers.

For example say I have used conditional formatting to highlight all cells between numbers 8 and 50. I now also want to highlight the number 6 in my range. Just 6. I don't want 7 etc.

So if I change my source to, say 6 to 50, I get 7 as well, which I don't want. So I'm looking for a formula to go back over the cells which have already been highlighted by the conditional formatting and also highlight those cells with 6 in them.

Hope that makes sense.
Cheers.

 

[barry houdini]

If you want 8-50 formatted the same as 6 then you can do it all within one conditional formatting condition, e.g. using "formula is"

=OR(P5=6,AND(P5>=8,P5<=50))

 

[jim may]

Using Conditional Formatting will work here for you AS LONG AS YOU
Example use Condition#1 to the most unique seting first, like
CellValue is equal to 6 (Red)
then
CellValue is between 1 to 10 (Green)
Give it a try,,

 

[jim may]

Using Conditional Formatting will work here for you AS LONG AS YOU
Example use Condition#1 to the most unique seting first, like
CellValue is equal to 6 (Red)
then
Condition #2:
CellValue is between 1 to 10 (Green)
Give it a try,,

 

[rjc4]

Hello Jim,
Perfect! Exactly what I was after. Was off on a different more complicated path. Thank you very much for your help. I didn't know you could use conditional formatting this way.
Cheers

Using Conditional Formatting will work here for you AS LONG AS YOU
Example use Condition#1 to the most unique seting first, like
CellValue is equal to 6 (Red)
then
Condition #2:
CellValue is between 1 to 10 (Green)
Give it a try,,

 

[rjc4]

Hi Barry,
Thanks for that. I obviously didn't think it through long enough. Your solution is what I was aiming for in the first place but got confused by the obvious. Jim's solution is equally effective so it's 50/50 which one to use. I'll try both. Many thanks.
Cheers

If you want 8-50 formatted the same as 6 then you can do it all within one conditional formatting condition, e.g. using "formula is"

=OR(P5=6,AND(P5>=8,P5<=50))