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

akparasite

Board Regular
Joined
Jul 15, 2002
Messages
55
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Howdy, which cell are the ones in question (ranges) and what is the fourth conditional value?
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,609
Members
452,785
Latest member
3110vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top