Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change background based on value in another column
Hi,
I have in column A options: a,b,c or d depending on what is chosen is it possible change the background color of the relevant row in B and C? i.e. if a3 = a then b3 & d3 background is red or if a3 = b then b3 & d3 background is green etc If possible is it possible to do this automatically i.e. not run a macro just on a cell change in A? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change background based on value in another column
MM,
You could use regular Excel functionality like conditional formatting (under format menu), however if you desire a code use the following. You should paste it somewhere in the appropriate sheet object Private Sub Worksheet_Change(ByVal Target As Range) 'Use Lcase(Target) if it should not be case sensitive Select Case Target Case Is = "a" Cells(Target.Row, 2).Interior.Color = vbRed Cells(Target.Row, 3).Interior.Color = vbRed Case Is = "b" Cells(Target.Row, 2).Interior.Color = vbGreen Cells(Target.Row, 3).Interior.Color = vbGreen Case Else 'No appropriate entry, clear color Cells(Target.Row, 2).Interior.ColorIndex = xlNone Cells(Target.Row, 3).Interior.ColorIndex = xlNone End Select End Sub HTH Brotha lee "MM User" wrote: Hi, I have in column A options: a,b,c or d depending on what is chosen is it possible change the background color of the relevant row in B and C? i.e. if a3 = a then b3 & d3 background is red or if a3 = b then b3 & d3 background is green etc If possible is it possible to do this automatically i.e. not run a macro just on a cell change in A? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change background based on value in another column
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range Dim iColor As Long Set R = Range("A1:A100") If Intersect(Target, R) Is Nothing Or Target.Count 1 Then Exit Sub Vals = Array("A", "B", "C", "D") Nums = Array(8, 9, 6, 3) For i = LBound(Vals) To UBound(Vals) If UCase(Target.Value) = Vals(i) Then iColor = Nums(i) Next With Target .Offset(0, 1).Interior.ColorIndex = iColor .Offset(0, 3).Interior.ColorIndex = iColor End With End Sub Note: you could do this with Conditional Formatting.........default color for a and three others for b, c, d If using Excel 2007 you have many more conditions. Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 20:50:25 -0000, "MM User" wrote: Hi, I have in column A options: a,b,c or d depending on what is chosen is it possible change the background color of the relevant row in B and C? i.e. if a3 = a then b3 & d3 background is red or if a3 = b then b3 & d3 background is green etc If possible is it possible to do this automatically i.e. not run a macro just on a cell change in A? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change background based on value in another column
Thanks Brotha,
I think I've got it! Regards "Brotha Lee" wrote in message ... MM, You could use regular Excel functionality like conditional formatting (under format menu), however if you desire a code use the following. You should paste it somewhere in the appropriate sheet object Private Sub Worksheet_Change(ByVal Target As Range) 'Use Lcase(Target) if it should not be case sensitive Select Case Target Case Is = "a" Cells(Target.Row, 2).Interior.Color = vbRed Cells(Target.Row, 3).Interior.Color = vbRed Case Is = "b" Cells(Target.Row, 2).Interior.Color = vbGreen Cells(Target.Row, 3).Interior.Color = vbGreen Case Else 'No appropriate entry, clear color Cells(Target.Row, 2).Interior.ColorIndex = xlNone Cells(Target.Row, 3).Interior.ColorIndex = xlNone End Select End Sub HTH Brotha lee "MM User" wrote: Hi, I have in column A options: a,b,c or d depending on what is chosen is it possible change the background color of the relevant row in B and C? i.e. if a3 = a then b3 & d3 background is red or if a3 = b then b3 & d3 background is green etc If possible is it possible to do this automatically i.e. not run a macro just on a cell change in A? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change background based on value in another column
Thanks Gord,
That great! "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range Dim iColor As Long Set R = Range("A1:A100") If Intersect(Target, R) Is Nothing Or Target.Count 1 Then Exit Sub Vals = Array("A", "B", "C", "D") Nums = Array(8, 9, 6, 3) For i = LBound(Vals) To UBound(Vals) If UCase(Target.Value) = Vals(i) Then iColor = Nums(i) Next With Target .Offset(0, 1).Interior.ColorIndex = iColor .Offset(0, 3).Interior.ColorIndex = iColor End With End Sub Note: you could do this with Conditional Formatting.........default color for a and three others for b, c, d If using Excel 2007 you have many more conditions. Gord Dibben MS Excel MVP On Tue, 16 Dec 2008 20:50:25 -0000, "MM User" wrote: Hi, I have in column A options: a,b,c or d depending on what is chosen is it possible change the background color of the relevant row in B and C? i.e. if a3 = a then b3 & d3 background is red or if a3 = b then b3 & d3 background is green etc If possible is it possible to do this automatically i.e. not run a macro just on a cell change in A? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change background based on value in another column
Thank's Brotha Lee!
I have another function I want to add to this if anyone could help me. I use a locked sheet with some unlocked cells and based on the value in A2 the cell B2, C2 aso should change between locked and unlocked along with the background. How do I do this? I Use Brotha Lee's example of changeing background. Thanks in advance! "Brotha Lee" wrote: MM, You could use regular Excel functionality like conditional formatting (under format menu), however if you desire a code use the following. You should paste it somewhere in the appropriate sheet object Private Sub Worksheet_Change(ByVal Target As Range) 'Use Lcase(Target) if it should not be case sensitive Select Case Target Case Is = "a" Cells(Target.Row, 2).Interior.Color = vbRed Cells(Target.Row, 3).Interior.Color = vbRed Case Is = "b" Cells(Target.Row, 2).Interior.Color = vbGreen Cells(Target.Row, 3).Interior.Color = vbGreen Case Else 'No appropriate entry, clear color Cells(Target.Row, 2).Interior.ColorIndex = xlNone Cells(Target.Row, 3).Interior.ColorIndex = xlNone End Select End Sub HTH Brotha lee "MM User" wrote: Hi, I have in column A options: a,b,c or d depending on what is chosen is it possible change the background color of the relevant row in B and C? i.e. if a3 = a then b3 & d3 background is red or if a3 = b then b3 & d3 background is green etc If possible is it possible to do this automatically i.e. not run a macro just on a cell change in A? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I change background colors based on criteria from another cel. | Excel Discussion (Misc queries) | |||
change background color based on range value | New Users to Excel | |||
Change background color based on value in column A | Excel Discussion (Misc queries) | |||
Change cell background based on multiple cells | Excel Worksheet Functions | |||
What is the macro to change background based on numeric cell value | Excel Worksheet Functions |