Conditional Formatting with VBA

[jamasm2010]

Hi,
I'm using XL2007 on W7 and trying to add conditional formatting based upon a running value. I (will) have a template that can have anywhere from 1 to 56 rows on it. In column H, I have prorammatically added a sum function as follows:

Worksheets(5).Cells(LastRow, 8).Formula = "=SUM($I" & LastRow & ":$AP" & LastRow & ")"

I would also like to add conditional formatting to each cell in column H ( 0 to 5 = red, 6 to 10, yellow, 11 to 15 = blue, and > = 16 would be green), so as the user enters values in column I through AP, the cell color in column H would reflect the total. Is it possible to add conditional formatting in the same manner as inserting the formula?

Can anyone help me out on this one?
Thanks,
James

 

[Claus Busch]

Hi James,

Am Sat, 18 Jan 2014 16:36:47 -0800 (PST) schrieb (e-mail address removed):

I would also like to add conditional formatting to each cell in column H ( 0 to 5 = red, 6 to 10, yellow, 11 to 15 = blue, and > = 16 would be green), so as the user enters values in column I through AP, the cell color in column H would reflect the total. Is it possible to add conditional formatting in the same manner as inserting the formula?

try follwing code in the code module of Sheet 5. When a cell in Range
I1:AP56 is changed, the result and the interior color in H changes:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("I1:AP56")) Is _
Nothing Then Exit Sub

Dim IColor As Long
Dim FColor As Long

With Cells(Target.Row, "H")
.Formula = "=SUM(I" & Target.Row & ":AP" & Target.Row & ")"

Select Case .Value
Case Is <= 5
IColor = vbRed
FColor = vbWhite
Case Is <= 10
IColor = vbYellow
FColor = xlAutomatic
Case Is <= 15
IColor = vbBlue
FColor = vbWhite
Case Else
IColor = vbGreen
FColor = xlAutomatic
End Select
.Interior.Color = IColor
.Font.Color = FColor
End With
End Sub




Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sun, 19 Jan 2014 09:31:01 +0100 schrieb Claus Busch:

Hi James,

better try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("I1:AP56")) Is _
Nothing Then Exit Sub

Dim IColor As Long
Dim FColor As Long

With Cells(Target.Row, "H")
.Formula = "=SUM(I" & Target.Row & ":AP" & Target.Row & ")"

Select Case .Value
Case Is <= 5
IColor = vbRed
FColor = vbWhite
Case Is <= 10
IColor = vbYellow
FColor = vbBlack
Case Is <= 15
IColor = vbBlue
FColor = vbWhite
Case Else
IColor = vbGreen
FColor = vbBlack
End Select
.Interior.Color = IColor
.Font.Color = FColor
End With
End Sub


Regards
Claus B.

 

[jamasm2010]

Hi,

I'm using XL2007 on W7 and trying to add conditional formatting based upon a running value. I (will) have a template that can have anywhere from 1 to 56 rows on it. In column H, I have prorammatically added a sum function as follows:



Worksheets(5).Cells(LastRow, 8).Formula = "=SUM($I" & LastRow & ":$AP" & LastRow & ")"



I would also like to add conditional formatting to each cell in column H ( 0 to 5 = red, 6 to 10, yellow, 11 to 15 = blue, and > = 16 would begreen), so as the user enters values in column I through AP, the cell color in column H would reflect the total. Is it possible to add conditional formatting in the same manner as inserting the formula?



Can anyone help me out on this one?

Thanks,

James

Hi Claus,
The code looks great and it looks like it's perfect, but I can't seem to get it to work. I put it in the sheet and then took it for a test drive and nothing happened, so I set a break point at the 'If' statement and it never hit it.
Any ideas?
James

 

[jamasm2010]

Hi,

I'm using XL2007 on W7 and trying to add conditional formatting based upon a running value. I (will) have a template that can have anywhere from 1 to 56 rows on it. In column H, I have prorammatically added a sum function as follows:



Worksheets(5).Cells(LastRow, 8).Formula = "=SUM($I" & LastRow & ":$AP" & LastRow & ")"



I would also like to add conditional formatting to each cell in column H ( 0 to 5 = red, 6 to 10, yellow, 11 to 15 = blue, and > = 16 would begreen), so as the user enters values in column I through AP, the cell color in column H would reflect the total. Is it possible to add conditional formatting in the same manner as inserting the formula?



Can anyone help me out on this one?

Thanks,

James

Hi Claus,
I did get it to work - finally (I was just being stupid!). Thank you so much for your help. I really appreciate it.
James