Conditional Formatting

[PHTech]

I want to add 5 conditional formatting to a workbook and Excel 2003 only allows 3, any suggestions?

 

[remybfg10k]

Excel 2007

 

[PHTech]

The thing is, at the company the spreadsheet needs to go to, ONLY uses MSO 2003... Have tried saving a MSO 2007 spreadsheet as Excel 2003, but it won't save it to use all 5 conditional fields...

Any other suggestions...? Even tried VB code, but it won't update the condition automatically, so... If someone knows a BASIC script for VB to do so, I would appreciate it...!

THANX...!

 

[HavocXphere]

You can try combining the conditions in a field with "and". Or even on an extra field in the spreadsheet.

 

[PHTech]

You can try combining the conditions in a field with "and". Or even on an extra field in the spreadsheet.

I need to use formatting for more than 3 colours...

With "and" it will only limit me still to use ONLY 3 formats to display... With the AND I can specify the fields to use for validating, but not more than 3 formats of colouring...

Thanx....

 

[mercurial]

You'll probably need to code it rather than use the conditional formatting that's built into Excel.

 

[greggpb]

This is just a quick and dirty... it will be slow but will work

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim CellValue As String
    CellValue = Target.Text
    Select Case CellValue
        Case "1"
            Target.Interior.ColorIndex = 1
        Case "3"
            Target.Interior.ColorIndex = 3
        Case Else
            Target.Interior.ColorIndex = 0
               
    End Select
End Sub

 

[PHTech]

This code also seems not be working...

 

[PHTech]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select
'Apply the color
rng.Interior.ColorIndex = Num
rng.Font.ColorIndex = black
Next rng
End Sub

When Entering info physically into the Specified cells, it do conditional formatting... BUT when I use a FORMULA to update info in the conditional formatted field, it won't update for instance the colour...? Any ideas where I should change the code to change the colour when the VALUE of the cell has changed within the formula...?

Thanx...

 

[Nod]

Maybe this will help?
http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

 

[PHTech]

It worked, BUT you still have to update the formula manually for it to display the correct colour... If you physically type in the info, the colours change accordingly, BUT if you use a formula to update the info in the cells, the colours does NOT change...