Change colour of cells depending on entry in one cell
In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to
change the background colour of another column (B) depending on what the entry is in column F. e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10 = no change |
Change colour of cells depending on entry in one cell
One way:
Assuming your column Fvalues are entered manually, put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rArea As Range Dim rCell As Range Dim nColor As Long Set Target = Intersect(Target, Range("F:F")) If Not Target Is Nothing Then For Each rArea In Target For Each rCell In rArea Select Case rCell.Value Case 0, 100 nColor = RGB(255, 0, 0) Case 30 nColor = RGB(0, 0, 255) Case 60 nColor = RGB(255, 102, 0) Case 90 nColor = RGB(0, 255, 0) Case Else nColor = -1 End Select If Not nColor = -1 Then _ rCell.Offset(0, -5).Interior.Color = nColor Next rCell Next rArea End If End Sub In article , harwookf wrote: In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to change the background colour of another column (B) depending on what the entry is in column F. e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10 = no change |
Change colour of cells depending on entry in one cell
Thanks for the speedy response. I think I am missing something though.
I have copied and pasted this information into the worksheet code module, but it doesn't change the colour of anything. Do I need to do something else? Regards harwookf "JE McGimpsey" wrote: One way: Assuming your column Fvalues are entered manually, put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rArea As Range Dim rCell As Range Dim nColor As Long Set Target = Intersect(Target, Range("F:F")) If Not Target Is Nothing Then For Each rArea In Target For Each rCell In rArea Select Case rCell.Value Case 0, 100 nColor = RGB(255, 0, 0) Case 30 nColor = RGB(0, 0, 255) Case 60 nColor = RGB(255, 102, 0) Case 90 nColor = RGB(0, 255, 0) Case Else nColor = -1 End Select If Not nColor = -1 Then _ rCell.Offset(0, -5).Interior.Color = nColor Next rCell Next rArea End If End Sub In article , harwookf wrote: In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to change the background colour of another column (B) depending on what the entry is in column F. e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10 = no change |
Change colour of cells depending on entry in one cell
Evidently...
Are you changing the values in column F? IF you set a breakpoint in the code, does it get executed? In article , harwookf wrote: Thanks for the speedy response. I think I am missing something though. I have copied and pasted this information into the worksheet code module, but it doesn't change the colour of anything. Do I need to do something else? Regards harwookf "JE McGimpsey" wrote: One way: Assuming your column Fvalues are entered manually, put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rArea As Range Dim rCell As Range Dim nColor As Long Set Target = Intersect(Target, Range("F:F")) If Not Target Is Nothing Then For Each rArea In Target For Each rCell In rArea Select Case rCell.Value Case 0, 100 nColor = RGB(255, 0, 0) Case 30 nColor = RGB(0, 0, 255) Case 60 nColor = RGB(255, 102, 0) Case 90 nColor = RGB(0, 255, 0) Case Else nColor = -1 End Select If Not nColor = -1 Then _ rCell.Offset(0, -5).Interior.Color = nColor Next rCell Next rArea End If End Sub In article , harwookf wrote: In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to change the background colour of another column (B) depending on what the entry is in column F. e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10 = no change |
Change colour of cells depending on entry in one cell
Oops, I'd changed the column. Silly billy, or words to that effect.
Anyway, now that I have the correct column, it comes up with an error and is highlighting the line:- rCell.Offset(0, -5).Interior.Color = nColor "JE McGimpsey" wrote: Evidently... Are you changing the values in column F? IF you set a breakpoint in the code, does it get executed? In article , harwookf wrote: Thanks for the speedy response. I think I am missing something though. I have copied and pasted this information into the worksheet code module, but it doesn't change the colour of anything. Do I need to do something else? Regards harwookf "JE McGimpsey" wrote: One way: Assuming your column Fvalues are entered manually, put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rArea As Range Dim rCell As Range Dim nColor As Long Set Target = Intersect(Target, Range("F:F")) If Not Target Is Nothing Then For Each rArea In Target For Each rCell In rArea Select Case rCell.Value Case 0, 100 nColor = RGB(255, 0, 0) Case 30 nColor = RGB(0, 0, 255) Case 60 nColor = RGB(255, 102, 0) Case 90 nColor = RGB(0, 255, 0) Case Else nColor = -1 End Select If Not nColor = -1 Then _ rCell.Offset(0, -5).Interior.Color = nColor Next rCell Next rArea End If End Sub In article , harwookf wrote: In one column (F), I have data like 0, 10, 30, 60, 90 and 100. I need to change the background colour of another column (B) depending on what the entry is in column F. e.g. f2 = 0 or 100 then a2 = red, 30 = blue, 60 = orange, 90 = green, 10 = no change |
Change colour of cells depending on entry in one cell
You probably need to change the offset, too.
But you didn't give any info about either what error, or what columns you changed to, so it's pretty hard to guess what change you need... In article , harwookf wrote: Oops, I'd changed the column. Silly billy, or words to that effect. Anyway, now that I have the correct column, it comes up with an error and is highlighting the line:- rCell.Offset(0, -5).Interior.Color = nColor |
Change colour of cells depending on entry in one cell
I'm now using Column F for the 0, 10, etc., but I need to highlighy the cells
in Column A (not B). When I change a value, it shows a run-time error '1004': Application-defined or object-defined error, then it gives me the option to End or Debug. If I Debug, it has the following line highlighted. rCell.Offset(0, -5).Interior.Color = nColor Does this help? "JE McGimpsey" wrote: You probably need to change the offset, too. But you didn't give any info about either what error, or what columns you changed to, so it's pretty hard to guess what change you need... In article , harwookf wrote: Oops, I'd changed the column. Silly billy, or words to that effect. Anyway, now that I have the correct column, it comes up with an error and is highlighting the line:- rCell.Offset(0, -5).Interior.Color = nColor |
Change colour of cells depending on entry in one cell
I'm now using Column F for the 0, 10, etc., but I need to highlighy the cells
in Column A (not B). When I change a value, it shows a run-time error '1004': Application-defined or object-defined error, then it gives me the option to End or Debug. If I Debug, it has the following line highlighted. rCell.Offset(0, -5).Interior.Color = nColor I copied and pasted it so it is exactly as given, though I have now changed the F:F to E:E, but it still doesn't work. Can you help me anymore? "JE McGimpsey" wrote: You probably need to change the offset, too. But you didn't give any info about either what error, or what columns you changed to, so it's pretty hard to guess what change you need... In article , harwookf wrote: Oops, I'd changed the column. Silly billy, or words to that effect. Anyway, now that I have the correct column, it comes up with an error and is highlighting the line:- rCell.Offset(0, -5).Interior.Color = nColor |
Change colour of cells depending on entry in one cell
I have changed the offset, so it now works, but can it be modified so that if
the numbers are anything other than 0, 30, 60, 90 or 100, it will default to the standard no fill? Also, will this still work if the spreadsheet is shared? Many thanks "JE McGimpsey" wrote: You probably need to change the offset, too. But you didn't give any info about either what error, or what columns you changed to, so it's pretty hard to guess what change you need... In article , harwookf wrote: Oops, I'd changed the column. Silly billy, or words to that effect. Anyway, now that I have the correct column, it comes up with an error and is highlighting the line:- rCell.Offset(0, -5).Interior.Color = nColor |
Change colour of cells depending on entry in one cell
One way:
Change: If Not nColor = -1 Then _ rCell.Offset(0, -5).Interior.Color = nColor To If Not nColor = -1 Then rCell.Offset(0, -5).Interior.Color = nColor Else rCell.Offset(0, -5).Interior.ColorIndex = _ xlColorIndexNone End If In article , harwookf wrote: I have changed the offset, so it now works, but can it be modified so that if the numbers are anything other than 0, 30, 60, 90 or 100, it will default to the standard no fill? |
All times are GMT +1. The time now is 02:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com