Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting with VBA
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |