Conditional Formatting 5 values?

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Mar 3, 2004

• #2

you need vba - the syntax is fairly straightforward - search the board for "vba more than three" and / or "select case" for some direction - post back if you're having problems.

 

Upvote 0

M

mrkntdrk

New Member

Joined

Feb 29, 2004

Messages

34

• Mar 3, 2004

• #3

Thanks... I will.

 

Upvote 0

N

Nimrod

MrExcel MVP

Joined

Apr 29, 2002

Messages

6,259

• Mar 3, 2004

• #4

Heres an example that changes colors for the value A,B,C,D,E

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case UCase(Target.Value)
Case "A"
Target.Interior.ColorIndex = 6
Case "B"
Target.Interior.ColorIndex = 20
Case "C"
Target.Interior.ColorIndex = 24
Case "D"
Target.Interior.ColorIndex = 35
Case "E"
Target.Interior.ColorIndex = 42
Case Else
Target.Interior.ColorIndex = 0
End Select
End Sub

TO INSTALL:
1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

 

Upvote 0

E

Ekim

Well-known Member

Joined

Jun 30, 2002

Messages

1,416

• Mar 3, 2004

• #5

You can get up to 6 font colours with a combination of Conditional Formatting (3) and Format/Cells/Number/Custom. In your target worksheet, format one target cell and then use the Paintbrush tool to copy the formats to all other target cells.

For example:

< 0 Red

0 - 10 Green

11 - 20 Blue

21 - 30 Orange

31 - 40 Light Purple

+ 40 Black (default)

Use Custom Formatting for < 0, 0 - 10 and + 40:

[Red][<0](#,##0.00);[Green][<10]0.00_);#,##0.00_);@

Use Conditional Formatting for:

Cell value between 11 - 20
Cell value between 21 - 30
Cell value between 31 – 40

HTH

Mike

 

Upvote 0

M

mrkntdrk

New Member

Joined

Feb 29, 2004

Messages

34

• Mar 4, 2004

• #6

I really like the idea of using Nimrods code. But it gives me an error when I copy and paste any of the values. What am I doing wrong?

Run-time error 13 Type mismatch

*****************************
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case UCase(Target.Value)
Case "A"
Target.Interior.ColorIndex = 6
Case "B"
Target.Interior.ColorIndex = 20
Case "C"
Target.Interior.ColorIndex = 24
Case "D"
Target.Interior.ColorIndex = 35
Case "E"
Target.Interior.ColorIndex = 42
Case Else
Target.Interior.ColorIndex = 0
End Select
End Sub
***************
Using Windows 2000, Office 2002 sp2

Thanks

 

Upvote 0

M

mrkntdrk

New Member

Joined

Feb 29, 2004

Messages

34

• Mar 4, 2004

• #7

Still stuck... anyone?

 

Upvote 0

TradBum

New Member

Joined

Mar 4, 2004

Messages

15

• Mar 4, 2004

• #8

Nimrod's code refers to the literal entry of A, B, C, D or E.

You need to change the Alpha references in Nimrod's example to whatever your conditions might be, as in:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case UCase(Target.Value)
        Case "Condition1"
            Target.Interior.ColorIndex = 6
        Case "Condition2"
            Target.Interior.ColorIndex = 20
        Case "Condition3"
            Target.Interior.ColorIndex = 24
        Case "Condition4"
            Target.Interior.ColorIndex = 35
        Case "Condition4"
            Target.Interior.ColorIndex = 42
        Case Else
            Target.Interior.ColorIndex = 0
        End Select
End Sub

Hope that helps,

Smitty

Heya Nimrod! Been to the Chief lately?

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,300

• Mar 4, 2004

• #9

Correct me if I'm wrong, but it looks like the cells you want to format actually house formulas, so the Worksheet_Change event would not be as effective, and maybe not work at all in your case, depending on the layout of the spreadsheet. But a Worksheet_Calculate event might, so look into that. If you are not sure, then post back and please specify what range of cells you want to format that have those VLOOKUP formulas.

 

Upvote 0

M

mrkntdrk

New Member

Joined

Feb 29, 2004

Messages

34

• Mar 5, 2004

• #10

Tom,

That must be it... not sure how to do that.
This is what I have. Values coming back like "Tom" "Sue" "Jan" "Joe" "Jim" "Jon".

cell H2
=IF(ISNA(VLOOKUP(H27,AI1:AJ1000,2,0)),"",VLOOKUP(H27,AI1:AJ1000,2,0))

cell K2
=IF(ISNA(VLOOKUP(K27,AL1:AM4250,2,0)),"",VLOOKUP(K27,AL1:AM4250,2,0))

cell N2
=IF(ISNA(VLOOKUP(N27,AO1:AP17590,2,0)),"",VLOOKUP(N27,AO1:AP17590,2,0))

Cell Q2
=IF(ISNA(VLOOKUP(Q27,AR1:AS42790,2,0)),"",VLOOKUP(Q27,AR1:AS42790,2,0))

Cell T2
=IF(ISNA(VLOOKUP(T27,AU1:AV63537,2,0)),"",VLOOKUP(T27,AU1:AV63537,2,0))


Thanks for the help.

 

Upvote 0