Excel cell colours

  ArrGee 12:45 20 Jul 2006
Locked

Is there anyway of automatically changing cell background colours depending on the value within a cell? As an example, if the value is 0, the cell stays white. If the value is 10 or over, it changes to a yellow. If the value is 20 or over it changes to red..etc etc.?

  stlucia 12:48 20 Jul 2006

Yes, it's conditional formatting. Click on Format, then Conditional Formatting.

  ArrGee 12:53 20 Jul 2006

Cheers for that, but this only allows upto 3 conditions.

  VoG II 13:13 20 Jul 2006

You may be able to adapt click here

  ArrGee 13:22 20 Jul 2006

Thanks stlucia. This will come in handy for some of the spreadsheets.

Thanks VoG, this seems as if it will do the trick.

  Durko 14:02 20 Jul 2006

Arrgee & Vog

I truly respect your excel expertise VOG but have you seen Datapig illustrate how to set up conditional formatting with Four conditions without need for code. What's your thoughts on his simple method?

Go to click here

Durko

  Durko 14:15 20 Jul 2006

VOG

I notice you directed Argee to 7 conditions using code. Forget my message and well done your working knowledge of all things Excel is remarkable.

D

  VoG II 14:19 20 Jul 2006

Thanks for that link - bookmarked.

Yes - that works and in fact I've said on a previous post ages ago that there are really four conditions if you count the default. However if you want more that four you really are in trouble and you need a macro like:

Sub ColourMe()
Dim txtArray, clrArray, cel As Range, i As Integer
txtArray = Array("Whisky", "Gin", "Rum", "Vodka", "Wine", "Beer", "Bourbon", "Pinacolada", "Pernod", "Water")
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

You just have to ensure that the number of items in txtArray and clrArray are the same.

  ArrGee 09:49 21 Jul 2006

Job done! Cheers guys.

  ArrGee 09:50 21 Jul 2006

Just marking 'resolved' (again!)

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

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?