IF cond. format statements for a range and when using more t

[akparasite]

I'm not a VBA expert, so I need a little guidance...

I've got all three default conditional format options loaded up in my worksheet, and I need to have a fourth available based upon whether a value is associated with a particular record. I've seen Dave Hawley's code for getting around the conditional format limit (see below), but I need to incorporate an IF/THEN statement that applies the formula I need with the fourth conditional format condition. I know this is probably REAL simple, but I'm not tuned into VBA enough to dial-in the structure. Any good ideas? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If

End Sub

 

[NateO]

Howdy, which cell are the ones in question (ranges) and what is the fourth conditional value?

 

[akparasite]

I probably shouldn't have put Dave's code in the message, as it is probably confusing.

I have the first three conditions defined within the typical Excel Format/Condiional Formatting application within each cell. the big question is I am not sure how to present the fourth condition using VBA for each of these cells (i.e. A2, B2, C2), based upon a condition in an adjacent cell (i.e. F2).

The fourth condition needs to be presented as formula saying somewhat like the following (for simplicity):

=OR(F2="Color Me", F2="I need a fourth condition")

The deal is I want it to determine this fourth condition color based upon values within the range (F:F), without having to name each cell explicitly in VBA. Does that make sense?

On another note, I could probably just do all four conditions in VBA (or may have to), I just don't know the code I'd need to write to cover all of the cells within fields A:A, B:B, C:C, and independent field F:F.

As a side note...do you know where I can get a list of the color code numbers (i.e. "1" is yellow, etc.) for VBA?

thanks for your help!

 

[NateO]

Hi AK, you may not need to, the following set of if statments may be of use for a fourth condition with the right string or some sort of wildcard, as an example:

<pre>
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [a2:c2]) Is Nothing Then
If Range("f" & Target.Row) = "Fourth Condition" And Target <> "" Then
Target.Interior.ColorIndex = 6
Else: Target.Interior.ColorIndex = xlNone
End If
End If
End Sub</pre>

You can find the pallete and associated index numbers by going into the visual basic editor, click the help icon, enter colorindex and click on ColorIndex Property

 

[akparasite]

Awesome...that's what I was looking for. I'll give it a shot! Thanks Nate!