Excel text colour based on cell contents

  montyburns 19:30 27 Feb 2006

Any Excel gurus who can help on this?

Working on a spreadsheet with lots of names in, and need to get cells to change colour/font characteristics depending on contents (which name is picked from drop down list)

Conditional formatting would be OK, except there are too many names....

Is there any way to do this using an "IF" formula for example?

All help appreciated!

  VoG II 19:46 27 Feb 2006

I think that you would need a macro like the following to do this:

Sub ColorThem()
Dim Ndx As Long
For Ndx = 1 To ActiveSheet.UsedRange.Rows.Count
If LCase(Cells(Ndx, 1).Value) = "account" Then
Rows(Ndx).Interior.ColorIndex = 3
End If
Next Ndx
End Sub

  montyburns 21:06 27 Feb 2006

You're something else mate!!

I posted this for my other half. I said "I've asked on PCAdvisor to see if anyone can sort this - I expect Vog will give us the answer..."

And you did! Well cool!

I'll get her to try it tomorrow and let you know how it goes!


  VoG II 21:27 27 Feb 2006

Sub ColourMe()
Dim txtArray, clrArray, flag As Boolean, cel As Range, i As Integer
txtArray = Array("Eliptical", "Gym", "Run", "Bike", "Turbo", "Swim", "Weights", "Rest", "Injury", "Core-strength")
clrArray = Array(24, 33, 38, 40, 36, 35, 34, 37, 39, 19)
For Each cel In ActiveSheet.UsedRange
For i = LBound(txtArray) To UBound(txtArray)
If cel.Value = txtArray(i) Then
cel.Font.ColorIndex = clrArray(i)
Exit For
End If
Next i
Next cel
End Sub

Is a better example. All you need to ensure is that there are the same number of values in clrArray as there are in txtArray.

  montyburns 11:18 28 Feb 2006

Just like to report that this worked like a charm!!

Cheers mate!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iPhone X review

How to find a font: Discover the name of a typeface with these apps

The best iPhone for 2017

Les meilleurs logiciels de montage vidéo gratuits (en 2017)