Conditional Formatting with VBA
 

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

Conditional Formatting with VBA
 

Hi James,

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Conditional Formatting with VBA
 

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Conditional Formatting with VBA
 

On Saturday, January 18, 2014 6:36:47 PM UTC-6, wrote:
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

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

Conditional Formatting with VBA
 

On Saturday, January 18, 2014 6:36:47 PM UTC-6, wrote:
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

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