Excel cell colours

  ArrGee 12:45 20 Jul 06
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 06

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

  ArrGee 12:53 20 Jul 06

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

  VoG II 13:13 20 Jul 06

You may be able to adapt click here

  ArrGee 13:22 20 Jul 06

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 06

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 06

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 06

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 06

Job done! Cheers guys.

  ArrGee 09:50 21 Jul 06

Just marking 'resolved' (again!)

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

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

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

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…