| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range color formatting
I have a column with multiple values. e.g. When the value of A1 changes, the
A1 changes color and the row (A1:A30) should change accordingly; and so on... I am using the If .Column = 1 Then Select Case and works beautiful for a cell. But I don't know how to extend it to a range. Where do I insert the range to match the color of that cell? Thanks for your help. Regards, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range color formatting
It would help if you post the code you use now.
"Myriam" wrote: I have a column with multiple values. e.g. When the value of A1 changes, the A1 changes color and the row (A1:A30) should change accordingly; and so on... I am using the If .Column = 1 Then Select Case and works beautiful for a cell. But I don't know how to extend it to a range. Where do I insert the range to match the color of that cell? Thanks for your help. Regards, |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range color formatting
Case 1
Range("A" & Target.row).Resize(1,30).Interior.ColorIndex = 3 ' or 'Range("A" & Target.row).Resize(30,1).Interior.ColorIndex = 3 I generally don't think of A1:A30 as a row, so I offered two options - you choose or if that doesn't appear to be what you want, perhaps a more definitive example. -- Regards, Tom Ogilvy "Myriam" wrote in message ... I have a column with multiple values. e.g. When the value of A1 changes, the A1 changes color and the row (A1:A30) should change accordingly; and so on... I am using the If .Column = 1 Then Select Case and works beautiful for a cell. But I don't know how to extend it to a range. Where do I insert the range to match the color of that cell? Thanks for your help. Regards, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range color formatting
Thanks for your response.
I'm sorry, I just realized I gave you the wrong ranges. The first 30 cells on row 1 would be A1 thru AD1. I need, for example, if A1 is a Yes, to change the color of A1 thru AD1. If A2 is No, change A2 thru AD2, etc. The code I'm using is: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then Select Case .value Case "YES": .Interior.ColorIndex = 25 .Font.ColorIndex = 2 Case "NO": .Interior.ColorIndex = 2 .Font.Color = RGB(255, 0, 0) Case "Maybe": .Interior.ColorIndex = 2 .Font.Color = RGB(255, 0, 0) End Select End If End With ws_exit: Application.EnableEvents = True End Sub "Tom Ogilvy" wrote: Case 1 Range("A" & Target.row).Resize(1,30).Interior.ColorIndex = 3 ' or 'Range("A" & Target.row).Resize(30,1).Interior.ColorIndex = 3 I generally don't think of A1:A30 as a row, so I offered two options - you choose or if that doesn't appear to be what you want, perhaps a more definitive example. -- Regards, Tom Ogilvy "Myriam" wrote in message ... I have a column with multiple values. e.g. When the value of A1 changes, the A1 changes color and the row (A1:A30) should change accordingly; and so on... I am using the If .Column = 1 Then Select Case and works beautiful for a cell. But I don't know how to extend it to a range. Where do I insert the range to match the color of that cell? Thanks for your help. Regards, |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range color formatting
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then Select Case .value Case "YES": .Resize(1,30).Interior.ColorIndex = 25 .Resize(1,30).Font.ColorIndex = 2 Case "NO": .Resize(1,30).Interior.ColorIndex = 2 .Resize(1,30).Font.Color = RGB(255, 0, 0) Case "Maybe": .Resize(1,30).Interior.ColorIndex = 2 .Resize(1,30).Font.Color = RGB(255, 0, 0) End Select End If End With ws_exit: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Myriam" wrote in message ... Thanks for your response. I'm sorry, I just realized I gave you the wrong ranges. The first 30 cells on row 1 would be A1 thru AD1. I need, for example, if A1 is a Yes, to change the color of A1 thru AD1. If A2 is No, change A2 thru AD2, etc. The code I'm using is: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then Select Case .value Case "YES": .Interior.ColorIndex = 25 .Font.ColorIndex = 2 Case "NO": .Interior.ColorIndex = 2 .Font.Color = RGB(255, 0, 0) Case "Maybe": .Interior.ColorIndex = 2 .Font.Color = RGB(255, 0, 0) End Select End If End With ws_exit: Application.EnableEvents = True End Sub "Tom Ogilvy" wrote: Case 1 Range("A" & Target.row).Resize(1,30).Interior.ColorIndex = 3 ' or 'Range("A" & Target.row).Resize(30,1).Interior.ColorIndex = 3 I generally don't think of A1:A30 as a row, so I offered two options - you choose or if that doesn't appear to be what you want, perhaps a more definitive example. -- Regards, Tom Ogilvy "Myriam" wrote in message ... I have a column with multiple values. e.g. When the value of A1 changes, the A1 changes color and the row (A1:A30) should change accordingly; and so on... I am using the If .Column = 1 Then Select Case and works beautiful for a cell. But I don't know how to extend it to a range. Where do I insert the range to match the color of that cell? Thanks for your help. Regards, |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
range color formatting
Thanks!
"Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then Select Case .value Case "YES": .Resize(1,30).Interior.ColorIndex = 25 .Resize(1,30).Font.ColorIndex = 2 Case "NO": .Resize(1,30).Interior.ColorIndex = 2 .Resize(1,30).Font.Color = RGB(255, 0, 0) Case "Maybe": .Resize(1,30).Interior.ColorIndex = 2 .Resize(1,30).Font.Color = RGB(255, 0, 0) End Select End If End With ws_exit: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Myriam" wrote in message ... Thanks for your response. I'm sorry, I just realized I gave you the wrong ranges. The first 30 cells on row 1 would be A1 thru AD1. I need, for example, if A1 is a Yes, to change the color of A1 thru AD1. If A2 is No, change A2 thru AD2, etc. The code I'm using is: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 1 Then Select Case .value Case "YES": .Interior.ColorIndex = 25 .Font.ColorIndex = 2 Case "NO": .Interior.ColorIndex = 2 .Font.Color = RGB(255, 0, 0) Case "Maybe": .Interior.ColorIndex = 2 .Font.Color = RGB(255, 0, 0) End Select End If End With ws_exit: Application.EnableEvents = True End Sub "Tom Ogilvy" wrote: Case 1 Range("A" & Target.row).Resize(1,30).Interior.ColorIndex = 3 ' or 'Range("A" & Target.row).Resize(30,1).Interior.ColorIndex = 3 I generally don't think of A1:A30 as a row, so I offered two options - you choose or if that doesn't appear to be what you want, perhaps a more definitive example. -- Regards, Tom Ogilvy "Myriam" wrote in message ... I have a column with multiple values. e.g. When the value of A1 changes, the A1 changes color and the row (A1:A30) should change accordingly; and so on... I am using the If .Column = 1 Then Select Case and works beautiful for a cell. But I don't know how to extend it to a range. Where do I insert the range to match the color of that cell? Thanks for your help. Regards, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Color formatting | Excel Worksheet Functions | |||
| Formatting the color of a range of cells based on the value of one cell | Excel Worksheet Functions | |||
| Color Formatting | Excel Discussion (Misc queries) | |||
| change fill color of a range of cells based on color of a cell? | Excel Programming | |||
| Conditional color formatting entries have wild color. | Excel Discussion (Misc queries) | |||
Hybrid Mode
