Range of Cells Conditional formatting based on:
I have data like this, 3 columns

H I J
1 1.67 1.37 2.00
2 4.63 1.37 2.00
3 2.17 1.25 1.50

If H1 is < I1 then color cell H1 Red
If H1 is J1 then color cell H1 Green
If H1 I1 and < J1 then cell H1 color Yellow
I need to continue this for all of H column Rage on a row by row basis, so
next would be column 2 calcs.

I got one part of this code to work, then it breaks - I think I need to have
more Pivot
language in it?
Sub Decorate()

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


Would really appreciate some ideas..

Thanks for help,

--
Regards,

Donica

Sub Decorate()
Range("H14:H500").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlLess, _
Formula1:="=$I14"
Selection.FormatConditions(1) _
.Interior.ColorIndex = 3
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=$J14"
Selection.FormatConditions(2) _
.Interior.ColorIndex = 4
End Sub
Worked fine for me.


"Donica" wrote:

Range of Cells Conditional formatting based on:
I have data like this, 3 columns

H I J
1 1.67 1.37 2.00
2 4.63 1.37 2.00
3 2.17 1.25 1.50

If H1 is < I1 then color cell H1 Red
If H1 is J1 then color cell H1 Green
If H1 I1 and < J1 then cell H1 color Yellow
I need to continue this for all of H column Rage on a row by row basis, so
next would be column 2 calcs.

I got one part of this code to work, then it breaks - I think I need to have
more Pivot
language in it?
Sub Decorate()

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


Would really appreciate some ideas..

Thanks for help,

--
Regards,

Donica

Sorry, left out the 3rd condition:

Sub Decorate()
Selection.FormatConditions.Delete
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlLess, _
Formula1:="=$I14"
Selection.FormatConditions(1) _
.Interior.ColorIndex = 3
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=$J14"
Selection.FormatConditions(2) _
.Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="=$I14", _
Formula2:="=$J14"
Selection.FormatConditions(3) _
.Interior.ColorIndex = 6
End Sub


worked for me.

--
Regards,
Tom Ogilvy


"Donica" wrote:

Range of Cells Conditional formatting based on:
I have data like this, 3 columns

H I J
1 1.67 1.37 2.00
2 4.63 1.37 2.00
3 2.17 1.25 1.50

If H1 is < I1 then color cell H1 Red
If H1 is J1 then color cell H1 Green
If H1 I1 and < J1 then cell H1 color Yellow
I need to continue this for all of H column Rage on a row by row basis, so
next would be column 2 calcs.

I got one part of this code to work, then it breaks - I think I need to have
more Pivot
language in it?
Sub Decorate()

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


Would really appreciate some ideas..

Thanks for help,

--
Regards,

Donica

Great to work with, I can't wait to try it
just out the door at the moment, so I'll try it a little later and let you
know how it works,

thank you
--
Regards,

Donica



"Tom Ogilvy" wrote:

Sorry, left out the 3rd condition:

Sub Decorate()
Selection.FormatConditions.Delete
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlLess, _
Formula1:="=$I14"
Selection.FormatConditions(1) _
.Interior.ColorIndex = 3
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=$J14"
Selection.FormatConditions(2) _
.Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="=$I14", _
Formula2:="=$J14"
Selection.FormatConditions(3) _
.Interior.ColorIndex = 6
End Sub


worked for me.

--
Regards,
Tom Ogilvy


"Donica" wrote:

Range of Cells Conditional formatting based on:
I have data like this, 3 columns

H I J
1 1.67 1.37 2.00
2 4.63 1.37 2.00
3 2.17 1.25 1.50

If H1 is < I1 then color cell H1 Red
If H1 is J1 then color cell H1 Green
If H1 I1 and < J1 then cell H1 color Yellow
I need to continue this for all of H column Rage on a row by row basis, so
next would be column 2 calcs.

I got one part of this code to work, then it breaks - I think I need to have
more Pivot
language in it?
Sub Decorate()

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


Would really appreciate some ideas..

Thanks for help,

--
Regards,

Donica

I just stuck the code in because I wanted to see what happened, and I
realized that it only works for one cell:

The goal is for Column Range H14:H200 to be formatted red yellow green based
on the other columns..

let me know if it is easy to stick a range in the code?
thank you again for all your help,
--
Regards,

Donica



"Tom Ogilvy" wrote:

Sorry, left out the 3rd condition:

Sub Decorate()
Selection.FormatConditions.Delete
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlLess, _
Formula1:="=$I14"
Selection.FormatConditions(1) _
.Interior.ColorIndex = 3
Selection.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="=$J14"
Selection.FormatConditions(2) _
.Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlBetween, _
Formula1:="=$I14", _
Formula2:="=$J14"
Selection.FormatConditions(3) _
.Interior.ColorIndex = 6
End Sub


worked for me.

--
Regards,
Tom Ogilvy


"Donica" wrote:

Range of Cells Conditional formatting based on:
I have data like this, 3 columns

H I J
1 1.67 1.37 2.00
2 4.63 1.37 2.00
3 2.17 1.25 1.50

If H1 is < I1 then color cell H1 Red
If H1 is J1 then color cell H1 Green
If H1 I1 and < J1 then cell H1 color Yellow
I need to continue this for all of H column Rage on a row by row basis, so
next would be column 2 calcs.

I got one part of this code to work, then it breaks - I think I need to have
more Pivot
language in it?
Sub Decorate()

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


Would really appreciate some ideas..

Thanks for help,

--
Regards,

Donica