Excel text colour based on cell contents

  montyburns 19:30 27 Feb 06

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 06

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 06

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 06

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 06

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

Cheers mate!

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

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Apple AirPods review: Apple's beautiful new Bluetooth headphones bring true intelligence to…