Conditional Formatting Range

[Donica]

Range of Cells Conditional formatting based on:
I have data like this, 3 columns
H I J
1.67 1.37 2.00
4.63 1.37 2.00
2.17 1.25 1.50

If column H Row 1 is < column I row 1 then color cell H1 Red
If Column H1 is > J1 then color cell H1 Green
If Column H1 > I1 and < J1 then cell color Yellow
I need to continue this for all of H column Rage on a row by row basis, so
next would be Row 2 calcs.
I cannot figure out how to do this in the Conditional formatting, and I’m
not sure about the code?
Would really appreciate some ideas..
Oh this is also in a changing pivot table, but the values are averaged to
they maintain the true values to be calc’d.
Thanks for help,

 

[Tom Ogilvy]

select H1:H200, H1 is the active cell

format=>Conditional format

for Condition 1:
Change Cell Value is to Formula is

in textbox put in

=And(H1>=I1,H1=<J1)
Click format, Patterns and pick you color (Yellow)

Click OK, then Add >>

for condition 2
Change the condition to Less Than, then in the box put
=I1
Click format, Patterns and pick you color (Red)
Click OK, then Add >>
for condition 3
Change the condition to Greater than, then in the box put
J1
Click format, Patterns and pick you color (Green)

and OK your way out.

Change the 200 above to indicate to select the data in column H where you
want the conditions applied.

 

[Donica]

Thanks for your help on this, I am still a little frustrated:

I think you code would work perfect if I was not using a pivot table,
because the conditional formatting does not seem to let me select a range -
only the field in the pivot. That won't work becuse I drop in the Greater
than I1, every column looks at I1, it will not drop to the next row? (row by
row).

I have managed to start some code thatseems to work the first time through:

Range("H14:H500").Select
Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$H14 < $I14"
Selection.FormatConditions(1).Interior.ColorIndex = 3

'Breaks -----
Selection.FormatConditions.Add Type:=xlExpression, _
Formula2:="=$H14 > $J14"
Selection.FormatConditions(1).Interior.ColorIndex = 4


If you can help me to get this to work or set up an example utilizing a
pivot, that would be awesome..

thanks for ideas and help on this