Conditional formatting based on meeting multiple criteria

[jlofthouse]

I am trying to do conditional formatting based on multiple criteria with some success, but cannot figure the whole thing out.

So far I've entered and been successful with the first 2 criteria using the following, but cannot get the 3rd criteria to work.
Criteria 1: =ISBLANK($F$2)
Criteria 2: =NOT(ISBLANK($F$2))

I also know that more than 3 criteria has to be done through VBA, but since I can't even get a 3rd basic criteria to work so I'm stuck. Most of what I've learned in Excel is self taught through trial and error so any help on figuring out the 3rd criteria as well as help on the more complicated VBA approach would be REALLY appreciated. I've searched the forums and have gotten lost amidst all the great advice on this board.

Here goes....

I have 5 columns (column E thru I) with the following headers: not programmed (E); programmed (F); notified (G); form sent (H); form returned (I). I want the whole row's writing to change color for certain instances and/or to also highlight if it meets other criteria.

1. if neither column E or F of a given row are populated (by an x"), I want row's writing to be Gray
2. if column F is populated, I want the writing for the entire row to change from grey to Black
3. If column G (in addition to column F) are populated, I want the writing for the entire row to change to Red
4. If column H is also populated (along with F & G), I want the whole row to "highlight yellow"
5. If column I of is also populated (along with F, G & H), then I want the whole row to "highlight green (with black writing)"

Thanks ever so much in advance to anyone who can help me out,

Julie

 

[lenze]

Hi and welcome to the Board!!
You are correct in assuming VBA for (Maybe 2007 could do it, but the rules would be hard to figure out) this!! I assume, that nos. 2-5 do not apply if no. 1 is True. Is that correct? Also, how are the cells F-I populated and are they always populated in Order? Answer these questions and we will see what can be done. It may be simpler than it sounds. I'm offline for a couple of hours, but will check back to see if you've received an answer, as this is an interesting problem
lenze

 

[jlofthouse]

Hi Lenze,

Thanks for the reply. Couple of things, including the answers to your return questions...

1. I am using Excel 2003
2. You are indeed correct that conditions 2-5 do not apply if condition 1 is not met.
3. I'm populating the cells with a good old fashioned "X" (to keep it simple...or so I hoped).
4. Columns "F" thru "I" will likely usually be filled out progressively (left to right), however should a given row/record's criteria change (a programmed record's form isn't returned so it is changed to "not programmed" and all relevant cells are changed accordingly) then that may not always be possible.
5. This formula is applied to upwards of 100 rows/records on a given worksheet (if that makes a difference).

Thanks so much!!!

 

[lenze]

Here is some example code!! It's probably not exactly what you want, but it should give us a start. Copy it to the WorkSheet module. (Right Click the sheet tab and choose "View Code"). It works on a doubleclick, placing(or removing) an "X" in the cell Doubleclicked. It then formats the Range. I know there will be some issues, but it should be a good basis to work with.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Column < 5 Or Target.Column > 9 Then Exit Sub
If Target = "" Then
    Target = "X"
Else:
   Target = ""
   Exit Sub
End If
Select Case Target.Column
Case 5: Cells(Target.Row, 5).Resize(1, 5).Font.ColorIndex = 15
Case 6
   Cells(Target.Row, 5).Resize(1, 5).Font.ColorIndex = xlAutomative
   Cells(Target.Row, 5).Resize(1, 5).Font.Bold = True
Case 7
   Cells(Target.Row, 5).Resize(1, 5).Font.ColorIndex = 3
   Cells(Target.Row, 5).Resize(1, 5).Font.Bold = True
Case 8
   Cells(Target.Row, 5).Resize(1, 5).Interior.ColorIndex = 6
Case 9
   Cells(Target.Row, 5).Resize(1, 5).Font.ColorIndex = xlAutomatic
   Cells(Target.Row, 5).Resize(1, 5).Interior.ColorIndex = 4
Case Else:
End Select
End Sub

I'm not sure about Column "E". I may have that reversed. Also, do you want to clear the formats at times? Lot to think about.
Play with it and get back with any questions or needed adjustments or changes.
lenze

 

[jlofthouse]

Hi Lenze,

Thanks! I'll try to apply it tomorrow (don't have the file at home). I promise to let you know how it goes.

Julie

 

[jlofthouse]

Hi Lenze,

I have copied the code into the page, but cannot get it to work. It is probably something I am doing since I have never worked with VBA before, and not a measure of the code you provided me.
I will try looking into it a bit more later, and let you know the outcome.

 

[lenze]

If nothing is happening when you doubleclick a cell in columns E:I, make sure Events are enabled. Open the VBE(ALT+F11). Choose View>Immediate Window. Type in
?Application.EnableEvents

This should return TRUE. If not enter
Application.EnableEvents = True
Now return to Excel and try again.

If that was not the problem, check to make sure you placed the code in the correct Worksheet module. Follow my instructions above
Good luck!!
lenze

 

[gdevi]

I am looking to do something similar.. and was using your code as a guideline/for ideas, but now am stuck.
Using conditional formatting, I could do SOME of it.. then thought I would try VBA.... which is where I got stuck.

Within a given area (b2 through j23) I would like the following conditions:

1) If any lower case letter is entered, the cell should be highlighted yellow (text can be any color)

2) If any upper case letter is entered, cell will be GREEN

3) if uppercase G is entered, cell will be green but no text will show up

4) if any numbers are entered, cell will be red.

5) if "unknown" is entered, cell will be grey with text

6) if NA is entered, cell will be grey, no text

any ideas/thoughts?
Thanks!

greta

 

[lenze]

gdevi

I would probably an If ElseIf approach instead of Select Case. Let me think about it. I do, however, strongly suggest you start a new thread instead of trying to tag on to this one. Although your question appears to be similar, it really is quite different.

lenze

 

[lenze]

gdevi: Try this in the Worksheet module. RightClick the sheet Tab and choose "View Code"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$B$2:$J$23")) Is Nothing Then Exit Sub
Dim myCol As Long
If UCase(Target) = "UNKNOWN" Then
   myCol = 15
ElseIf Target = "NA" Then
   myCol = 15
   Target.Font.ColorIndex = 15
ElseIf Target = LCase(Target) Then
   myCol = 6
ElseIf Target = UCase("G") Then
   myCol = 4
   Target.Font.ColorIndex = 4
ElseIf Target = UCase(Target) Then
   myCol = 4
ElseIf IsNumeric(Target) Then
   myCol = 3
End If
Target.Interior.ColorIndex = myCol
End Sub

lenze