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

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?