Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel spreadsheet change colour of cell back ground depending on d | Excel Discussion (Misc queries) | |||
how do i change cell background colour depending on its content? | Excel Discussion (Misc queries) | |||
function to count the amount of cells depending on part of the cell entry | Excel Worksheet Functions | |||
Changing cell colour depending on another cells value... | Excel Discussion (Misc queries) | |||
How can i change cell colour depending on month of date in cell? | Excel Discussion (Misc queries) |