Format based on cell value

[hotherps]

I have the following code that changes the font color of the cell red if
the value is lower in the previous cell, and if the value increases it
should be green. What I want to do is make it conditional based on a
text value in column C that preceeds each row.

for example if the value in cell C42 = "Cost" then I want the font to
turn red if the amount goes up.

But if the value in column C42 starts with "Production" I want to
reverse the condition, and use the green font.

I just can't get it to work, thanks


For Each cell_in_loop In Range("D42142")
If cell_in_loop.Value > cell_in_loop.Offset(0, -1) Then
With cell_in_loop.Offset(0, 0).Font
ColorIndex = 10
'.Pattern = xlSolid
End With
End If
If cell_in_loop.Value < cell_in_loop.Offset(0, -1) Then
With cell_in_loop.Offset(0, 0).Font
ColorIndex = 3
'.Bold = True
End With
End If
Next

Jim

 

[Frank Kabel]

Hi
try (utested)

Dim up_condition
Dim low_condition

For Each cell_in_loop In Range("D42142")
Select case cell_in_loop.offset(0,-1).value
case "Cost"
up_condition = 3
low_condition = 10
case "Production"
up_condition = 10
low_condition = 3
end select

If cell_in_loop.Value > cell_in_loop.Offset(-1, 0) Then
cell_in_loop.Font´.ColorIndex = up_condition
elseif cell_in_loop.Value < cell_in_loop.Offset(-1, 0) Then
cell_in_loop.Font´.ColorIndex = low_condition
End If
Next

 

[hotherps]

Frank,
Thanks for your help, your approach really makes sense to me. However
can't get it to run. It runs down to the IF statement under the En
Select statement. It highlights the row and says "Run time error 100
application defined or object defined error"



Thank

 

[Frank Kabel]

Hi
the following works for me (note: you're not allowed to start in row 1
with your range):

sub test_it()
Dim up_condition
Dim low_condition
Dim cell_in_loop As Range

For Each cell_in_loop In Range("D2142")
Select Case cell_in_loop.Offset(0, -1).Value
Case "Cost"
up_condition = 3
low_condition = 10
Case "Production"
up_condition = 10
low_condition = 3
End Select

If cell_in_loop.Value > cell_in_loop.Offset(-1, 0) Then
cell_in_loop.Font.ColorIndex = up_condition
ElseIf cell_in_loop.Value < cell_in_loop.Offset(-1, 0) Then
cell_in_loop.Font.ColorIndex = low_condition
End If
Next
End sub

 

[hotherps]

Frank,
I'm going to attach a snippet of what the file looks like. Right no
what is happening is that all of font on rows 1 and 2 are red, and th
other 3 rows are green. Notice I changed "Production" to "QPH" that i
the actual name.

I'm not sure what the problem is, are you saying that if the value goe
up week by week and then down that your font color is changing in th
same row by cell?

Mine is doing the whole row either all red or all green\

Should I Dim up_condition and down_condition as something?

Thanks again for your help

Attachment filename: format.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=48038

 

[hotherps]

FRank,

I got it to work, I changed the very last values in the IF statement
on the bottom. I just reversed the (0,-1) on bothand it works fine.

Thanks !!