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.

Huawei P10 review

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

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now