Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
Does anyone know how to have a spreadsheet automatically update text color in
an entire row when a specific cell is populated with a list of choices from a drop down menu? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
hym
try to see on the Chip Person web site, it could help you http://www.cpearson.com/excel/whatsnew.htm hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gallego" escreveu: Does anyone know how to have a spreadsheet automatically update text color in an entire row when a specific cell is populated with a list of choices from a drop down menu? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
HI, I looked and found nothing. Basically have a set of 11 choices from a
cell validation I have setup and I would like to have the entire row change text color based on choice. "Marcelo" wrote: hym try to see on the Chip Person web site, it could help you http://www.cpearson.com/excel/whatsnew.htm hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Gallego" escreveu: Does anyone know how to have a spreadsheet automatically update text color in an entire row when a specific cell is populated with a list of choices from a drop down menu? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
Gallego try this:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim Test As Double i = Target.Row Set rng = Range("A1").CurrentRegion For i = 1 To rng.Rows.Count Select Case Cells(i, 1) Case "a" rng.Rows(i).Interior.ColorIndex = 4 Case "b" rng.Rows(i).Interior.ColorIndex = 44 Case "c" rng.Rows(i).Interior.ColorIndex = 3 End Select Next i End Sub Hope this helps, -- Dan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
Oops, that should be:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range i = Target.Row Set rng = Range("A1").CurrentRegion For i = 1 To rng.Rows.Count Select Case Cells(i, 1) Case "a" rng.Rows(i).Font.ColorIndex = 4 Case "b" rng.Rows(i).Font.ColorIndex = 44 Case "c" rng.Rows(i).Font.ColorIndex = 3 End Select Next i End Sub -- Dan |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
Hi Dan,
Thanks. My data validation starts in column L. Would I have to put the color index on a separate sheet for this to work? "Dan Oakes" wrote: Oops, that should be: Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range i = Target.Row Set rng = Range("A1").CurrentRegion For i = 1 To rng.Rows.Count Select Case Cells(i, 1) Case "a" rng.Rows(i).Font.ColorIndex = 4 Case "b" rng.Rows(i).Font.ColorIndex = 44 Case "c" rng.Rows(i).Font.ColorIndex = 3 End Select Next i End Sub -- Dan |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
If your data validation starts in column L then you'll need to change
Select Case Cells(i, 1) to Select Case Cells(i, 12). And yes, for this code to work you'll have to go to the Visual Basic Editor and paste it in the sheet you want to use it with, not in a module. And obviously you'll also need to change "a", "b" and "c" to whatever your data is. -- Dan |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
Thanks, so I went in and added the following to my spreadsheet.
Notice on set rng L17:L3333.....I don't know what I am doing there. I did add my data though, example 0,1,&2 for the color change. You can choose the data validate beginning on cell L19 through about L3444, and I add to it daily. What code should I use for that? Also, when I selected L19 and changed it to 0, two rows down is where the color change took place based on what you see below as code. I need the first row to change color on row 19. What does the i mean? Do I need to substitute anything for the i? Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range i = Target.Row Set rng = Range("L17:L3333").CurrentRegion For i = 1 To rng.Rows.Count Select Case Cells(i, 12) Case "0" rng.Rows(i).Font.ColorIndex = 44 Case "1" rng.Rows(i).Font.ColorIndex = 42 Case "2" rng.Rows(i).Font.ColorIndex = 3 End Select Next i End Sub "Dan Oakes" wrote: If your data validation starts in column L then you'll need to change Select Case Cells(i, 1) to Select Case Cells(i, 12). And yes, for this code to work you'll have to go to the Visual Basic Editor and paste it in the sheet you want to use it with, not in a module. And obviously you'll also need to change "a", "b" and "c" to whatever your data is. -- Dan |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
My source for the data validation is in cells B4:B15.......I am not sure how
that can come into play. "Dan Oakes" wrote: If your data validation starts in column L then you'll need to change Select Case Cells(i, 1) to Select Case Cells(i, 12). And yes, for this code to work you'll have to go to the Visual Basic Editor and paste it in the sheet you want to use it with, not in a module. And obviously you'll also need to change "a", "b" and "c" to whatever your data is. -- Dan |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
Well since you only have 12 different values you might think about
using a delimited list, otherwise you could always put your source data on a hidden worksheet. Also, the rng needs to stay at "A1", that's probably why you are having problems. Try this, it's probably better suited for what you are doing: Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long i = Target.Row Set rng = Range("A1").CurrentRegion If Target.Column = 12 Then Select Case Cells(i, 12) Case "a" rng.Rows(i).Font.ColorIndex = 4 Case "b" rng.Rows(i).Font.ColorIndex = 44 Case "c" rng.Rows(i).Font.ColorIndex = 3 End Select End If End Sub Hope this helps, -- Dan |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
Is there anyway I can send you a sample of what the spreadsheet looks like so
I can get this right? :) thx. "Dan Oakes" wrote: Well since you only have 12 different values you might think about using a delimited list, otherwise you could always put your source data on a hidden worksheet. Also, the rng needs to stay at "A1", that's probably why you are having problems. Try this, it's probably better suited for what you are doing: Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long i = Target.Row Set rng = Range("A1").CurrentRegion If Target.Column = 12 Then Select Case Cells(i, 12) Case "a" rng.Rows(i).Font.ColorIndex = 4 Case "b" rng.Rows(i).Font.ColorIndex = 44 Case "c" rng.Rows(i).Font.ColorIndex = 3 End Select End If End Sub Hope this helps, -- Dan |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
And no you don't need to change the i, the i just signifies what the
current row is. -- Dan |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto text color
Just sent it back...
-- Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't format cell color/text color in Office Excel 2003 in fil | Excel Discussion (Misc queries) | |||
Cond. format text color based on another's text color | Excel Discussion (Misc queries) | |||
Cell Fill Color and text color - changes for recipient | Excel Discussion (Misc queries) | |||
Can't format cell color/text color in Office Excel 2003 in files . | Excel Discussion (Misc queries) | |||
Shortcut for FILL COLOR and COLOR TEXT | Excel Discussion (Misc queries) |