Auto text color

[Guest]

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?

 

[Guest]

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:

 

[Guest]

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.

 

[Dan Oakes]

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

 

[Dan Oakes]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[Guest]

My source for the data validation is in cells B4:B15.......I am not sure how
that can come into play.

 

[Dan Oakes]

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

 

[Guest]

Is there anyway I can send you a sample of what the spreadsheet looks like so
I can get this right?
thx.

 

[Dan Oakes]

And no you don't need to change the i, the i just signifies what the
current row is.

-- Dan

 

[Dan Oakes]

Sure...

(e-mail address removed)

-- Dan

 

[Guest]

It's on it's way. Thanks again. You been great.

 

[Dan Oakes]

Just sent it back...

-- Dan