Microsoft Excel formatting cells

  Tulips 21:25 19 Nov 2006

On the conditional formatting tab on a spreadsheet it only allows you to format three times per cell, is there any way that can be increased to six?

  VoG II 21:28 19 Nov 2006

With VBA click here

  Tulips 21:45 19 Nov 2006

forgive me for being dumb but I can't see the code that I'm supposed to paste.

  VoG II 21:47 19 Nov 2006

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error Goto 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "Tom", "Joe", "Paul"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "Smith", "Jones"
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Case 1, 3, 7, 9
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 10 To 25
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case 26 To 99
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select

End Sub

  Tulips 22:57 19 Nov 2006

Copied and pasted but I've still only got three formats.

  VoG II 09:07 20 Nov 2006

Did you paste into the worksheet's code module - right click the sheet tab, View Code, paste in. Close the module.

Now, what happens if you type 10, 99, Tom and Smith into different cells?

  Tulips 17:54 20 Nov 2006

I'm still confused, Yes when I type 10,99,Tom and Smith they appear as they should but that still doesn't help me on my spread sheet where there are still only three formats.

  VoG II 13:28 21 Nov 2006

There are only three conditional formats available in Excel. To get around this you need a macro, similar to the one above. You will need to modify the macro to achieve the formatting that you want. Or state what your 6 conditions are and I'll have a go.

  VoG II 13:41 21 Nov 2006

Or see click here for 4 conditional formats.

  Tulips 18:25 21 Nov 2006

VoG, the 4 conditional formats is along the lines of what I'm doing but I need 6 (down to 6th wins) If you can sort anything out that would be great, Cheers for your time.

  VoG II 18:42 21 Nov 2006

Can you list the six conditions that you want 'conditional formats' for?

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

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

No need to scan sketches into your computer with Moleskine's new smart pen

How to use 3D Touch on iPhone

Comment importer des contacts d’un iPhone à un autre iPhone ?