|
Case Statement for Changing Font AND Background Color
Column A of my spreadsheet contains a VLOOKUP formula all the way down that
returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
Case Statement for Changing Font AND Background Color
On 18 Dec., 23:51, jjones wrote:
Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ Hi JJ End Sub have to be the last line in your macro. See other changes too. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Target.Font.ColorIndex = 3 icolor = 3 Case 2 Target.Font.ColorIndex = 46 icolor = 46 Case 3 Target.Font.ColorIndex = 6 icolor = 6 Case 4 Target.Font.ColorIndex = 4 icolor = 4 Case 5 Target.Font.ColorIndex = 5 icolor = 5 Case 6 Target.Font.ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub Regards, Per |
Case Statement for Changing Font AND Background Color
A few revisions should help.
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then With Target Select Case Target Case 1 .Font.ColorIndex = 3 icolor = 3 Case 2 .Font.ColorIndex = 46 icolor = 46 Case 3 .Font.ColorIndex = 6 icolor = 6 Case 4 .Font.ColorIndex = 4 icolor = 4 Case 5 .Font.ColorIndex = 5 icolor = 5 Case 6 .Font.ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select End With Target.Interior.ColorIndex = icolor End If End Sub Gord Dibben MS Excel MVP On Tue, 18 Dec 2007 14:51:00 -0800, jjones wrote: Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: _________________________________________________ _____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub _________________________________________________ _____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
Case Statement for Changing Font AND Background Color
It's not working. It works if I manually type a number in a cell, but it
doesn't work where all my VLOOKUP formulas are. I tried re-entering my formulas, recalculating, and even copying and just pasting the values. Oh, and I did change one thing. I needed this to work for all of column A, not just rows 1 - 10, so I typed If Not Intersect(Target, Range("A:A")) Is Nothing Then (etc...) I keep getting "Run-time error '13': Type mismatch". If I click "Debug" then the debugger stops on Case 1. "Per Jessen" wrote: On 18 Dec., 23:51, jjones wrote: Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ Hi JJ End Sub have to be the last line in your macro. See other changes too. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Target.Font.ColorIndex = 3 icolor = 3 Case 2 Target.Font.ColorIndex = 46 icolor = 46 Case 3 Target.Font.ColorIndex = 6 icolor = 6 Case 4 Target.Font.ColorIndex = 4 icolor = 4 Case 5 Target.Font.ColorIndex = 5 icolor = 5 Case 6 Target.Font.ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub Regards, Per |
Case Statement for Changing Font AND Background Color
Hi Don
You seem to have taken a different approach to my problem. I'm intrigued but I don't really understand what your code is saying. I did expand the range to include all of column A...maybe I screwed it up when I did that. I entered: Sub docolor() For Each c In Range("a:a") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub It does seem to respond to my VLOOKUP cells, but all I get is green (color code 4). Was I supposed to add something else? "Don Guillett" wrote: Try this. Adjust colors to suit from color palette numbers. Sub docolor() For Each c In Range("a11:a16") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
Case Statement for Changing Font AND Background Color
Don't use the whole column and do use the on error statement. As written, it
is only looking up 1,2,3,4,5,6 Sub docolor() On Error Resume Next lr = Cells(Rows.Count, "a").End(xlUp).Row For Each c In Range("a1:a" & lr) x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Hi Don You seem to have taken a different approach to my problem. I'm intrigued but I don't really understand what your code is saying. I did expand the range to include all of column A...maybe I screwed it up when I did that. I entered: Sub docolor() For Each c In Range("a:a") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub It does seem to respond to my VLOOKUP cells, but all I get is green (color code 4). Was I supposed to add something else? "Don Guillett" wrote: Try this. Adjust colors to suit from color palette numbers. Sub docolor() For Each c In Range("a11:a16") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
Case Statement for Changing Font AND Background Color
Now we're getting somewhere! :) Just one glitch. It doesn't seem to execute
automatically. I right-clicked on the sheet and clicked on "View Code". That's where I have your code pasted. The cells don't change colors unless I go back in to this VB screen and click the little "play" button to run the code. Can't this fire on it's own? "Don Guillett" wrote: Don't use the whole column and do use the on error statement. As written, it is only looking up 1,2,3,4,5,6 Sub docolor() On Error Resume Next lr = Cells(Rows.Count, "a").End(xlUp).Row For Each c In Range("a1:a" & lr) x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Hi Don You seem to have taken a different approach to my problem. I'm intrigued but I don't really understand what your code is saying. I did expand the range to include all of column A...maybe I screwed it up when I did that. I entered: Sub docolor() For Each c In Range("a:a") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub It does seem to respond to my VLOOKUP cells, but all I get is green (color code 4). Was I supposed to add something else? "Don Guillett" wrote: Try this. Adjust colors to suit from color palette numbers. Sub docolor() For Each c In Range("a11:a16") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
Case Statement for Changing Font AND Background Color
It can be made to fire with the worksheet_calculate event but I don't
recommend it. I would assign to a shape from the drawing toolbar or a button from the forms toolbar. -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Now we're getting somewhere! :) Just one glitch. It doesn't seem to execute automatically. I right-clicked on the sheet and clicked on "View Code". That's where I have your code pasted. The cells don't change colors unless I go back in to this VB screen and click the little "play" button to run the code. Can't this fire on it's own? "Don Guillett" wrote: Don't use the whole column and do use the on error statement. As written, it is only looking up 1,2,3,4,5,6 Sub docolor() On Error Resume Next lr = Cells(Rows.Count, "a").End(xlUp).Row For Each c In Range("a1:a" & lr) x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Hi Don You seem to have taken a different approach to my problem. I'm intrigued but I don't really understand what your code is saying. I did expand the range to include all of column A...maybe I screwed it up when I did that. I entered: Sub docolor() For Each c In Range("a:a") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub It does seem to respond to my VLOOKUP cells, but all I get is green (color code 4). Was I supposed to add something else? "Don Guillett" wrote: Try this. Adjust colors to suit from color palette numbers. Sub docolor() For Each c In Range("a11:a16") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
Case Statement for Changing Font AND Background Color
Thanks Don. I guess I'll just make this code part of some other macros that
will be running. Appreciate the help... --JJ "Don Guillett" wrote: It can be made to fire with the worksheet_calculate event but I don't recommend it. I would assign to a shape from the drawing toolbar or a button from the forms toolbar. -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Now we're getting somewhere! :) Just one glitch. It doesn't seem to execute automatically. I right-clicked on the sheet and clicked on "View Code". That's where I have your code pasted. The cells don't change colors unless I go back in to this VB screen and click the little "play" button to run the code. Can't this fire on it's own? "Don Guillett" wrote: Don't use the whole column and do use the on error statement. As written, it is only looking up 1,2,3,4,5,6 Sub docolor() On Error Resume Next lr = Cells(Rows.Count, "a").End(xlUp).Row For Each c In Range("a1:a" & lr) x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Hi Don You seem to have taken a different approach to my problem. I'm intrigued but I don't really understand what your code is saying. I did expand the range to include all of column A...maybe I screwed it up when I did that. I entered: Sub docolor() For Each c In Range("a:a") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub It does seem to respond to my VLOOKUP cells, but all I get is green (color code 4). Was I supposed to add something else? "Don Guillett" wrote: Try this. Adjust colors to suit from color palette numbers. Sub docolor() For Each c In Range("a11:a16") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
Case Statement for Changing Font AND Background Color
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Thanks Don. I guess I'll just make this code part of some other macros that will be running. Appreciate the help... --JJ "Don Guillett" wrote: It can be made to fire with the worksheet_calculate event but I don't recommend it. I would assign to a shape from the drawing toolbar or a button from the forms toolbar. -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Now we're getting somewhere! :) Just one glitch. It doesn't seem to execute automatically. I right-clicked on the sheet and clicked on "View Code". That's where I have your code pasted. The cells don't change colors unless I go back in to this VB screen and click the little "play" button to run the code. Can't this fire on it's own? "Don Guillett" wrote: Don't use the whole column and do use the on error statement. As written, it is only looking up 1,2,3,4,5,6 Sub docolor() On Error Resume Next lr = Cells(Rows.Count, "a").End(xlUp).Row For Each c In Range("a1:a" & lr) x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Hi Don You seem to have taken a different approach to my problem. I'm intrigued but I don't really understand what your code is saying. I did expand the range to include all of column A...maybe I screwed it up when I did that. I entered: Sub docolor() For Each c In Range("a:a") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub It does seem to respond to my VLOOKUP cells, but all I get is green (color code 4). Was I supposed to add something else? "Don Guillett" wrote: Try this. Adjust colors to suit from color palette numbers. Sub docolor() For Each c In Range("a11:a16") x = Application.Choose(c, 3, 46, 6, 4, 5, 13) c.Interior.ColorIndex = x c.Font.ColorIndex = x Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "jjones" wrote in message ... Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
Case Statement for Changing Font AND Background Color
I know that conditional formatting limits me to 3 conditions,
Hi. Just to mention since I didn't see a version listed. In Excel 2007, a nice option is to select "Conditional Formatting" and select "Color Scales." The Green-Yellow-Red is a nice option. It also appears to match your color requirement where the low numbers are red (ie 1), and working its way to green for the higher numbers (ie 6) -- HTH Dana DeLouis "jjones" wrote in message ... Column A of my spreadsheet contains a VLOOKUP formula all the way down that returns one of six numerical answers (i.e. the number 1, 2, 3, 4, 5, or 6). I want these numbers there for sorting purposes, but I don't want to actually see them. Instead I want to see a "color code" all the way down. So if the value is 1, then I want the background color and the font for that cell to be red. If 2, then orange, etc... I know that conditional formatting limits me to 3 conditions, but I'm sure that I can write some sort of CASE statement to do the same thing. I've found several posts similar to what I'm looking for, but not exact. I tried to piece them together, but since my VB skills leave alot to be desired, I need some help to pull this off. What I have is something like this: __________________________________________________ ____ Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 Font.ColorIndex = 3 icolor = 3 Case 2 Font.ColorIndex = 46 icolor = 46 Case 3 Font.ColorIndex = 6 icolor = 6 Case 4 ColorIndex = 4 icolor = 4 Case 5 Font.ColorIndex = 5 icolor = 5 Case 6 ColorIndex = 13 icolor = 13 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub __________________________________________________ ____ It doesn't seem to do anything. Can someone tell me how this code should be written? Thanks in advance, JJ |
| All times are GMT +1. The time now is 07:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com