# Conditional Formatting Using Color

#### ststern45

##### Well-known Member
Excel has a limit of 3 for conditional formatting.

I would like to know if it is possible conditional format more than 3 times with VBA:

1. If cell a1 = cell range b1:b100 then format it in RED

2. If cell a1 = cell range c1:c100 then format it in Green

3. If cell a1 = cell range d1:d100 then format it in Blue

4. If cell a1 = cell range e1:e100 then format it in Yellow

Steve

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I would like to know if it is possible conditional format more than 3 times with VBA:
Hello Steve,
Sure, this is possible. (About as many conditions as you can think of using vba.)
I don't quite I understand your criteria.
Are you saying:
"If a1 is equal to the sum of B1:B100 then be red"?
Or:
"If the value in a1 is found anywhere within the range B1:B100 then be red"?
Or . . (?)

And you should know that when testing a1 against all those columns, your result will be
the last true condition it finds (unless told to stop at the first) so the order in which you
do the testing will be something to pay attention to.

Conditional Formatting

Sorry for the confusion.

Conditional format formula

=countif(\$b\$1:\$b\$100,a1) RED

=countif(\$c\$1:\$c\$100,a1) Blue

=countif((\$d\$1:\$d\$100,a1) Yellow

Can't do a 4th Conditional Format

=countif(\$e\$1:\$e\$100,a1) Green

Thanks,

Steve

do you mean if A1 is found in ... then color accordingly ?

Hello Steve,
Assuming the answer to Erik's question is yes, then perhaps this will help.
It will go through the rows 1:100 in each of the columns B:E to test for the existence of the
value in A1.
If that value is found it will stop looking (on the first instance if there are more than one)
and color the font of A1 with the appropriate color.
It assumes the sheet it's searching through is the active sheet.
Code:
``````Sub FormatDemo()
Dim i As Integer, Rng As Range

For i = 2 To 5
Set Rng = Range(Cells(1, i), Cells(100, i))
If Application.WorksheetFunction.CountIf(Rng, [A1]) > 0 Then Exit For
Next i

Select Case i
Case 2
[A1].Font.ColorIndex = 3 'Red
Case 3
[A1].Font.ColorIndex = 5 'Blue
Case 4
[A1].Font.ColorIndex = 6 'Yellow
Case 5
[A1].Font.ColorIndex = 4 'Green
Case Else
[A1].Font.ColorIndex = xlAutomatic
End Select

End Sub``````

[EDIT:]
Oh yeah, how is the value in A1 getting entered?
Is it being entered by (1) manual entry? (2) pasting - (manually or with vba)? or
(3) is it the return of a formula?

We can make this code run automatically (and therefore work just like a true conditionsl
formatting) if we know how the value is entered.

Conditional Formatting

The numbers are entered manually.

There are a total of 30 different numbers in cells a1 through a30.

Steve

OK, the values are entered manually - which means the worksheet change event could
be used.
Am I now to understand you want to loop down column A from rows 1 to 30?
(For the proper solution to your situation, the more details you can provide in the
beginning, the more likely we will be to be able to come up with something useful.) :wink:

If my guess is correct then this in the sheet code module for the sheet of interest should
do what you're after. (You can get rid of the other code altogether.)
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A30")) Is Nothing Then Exit Sub

Dim i As Integer, Rng As Range, c As Range

For Each c In Range("A1:A30")
For i = 2 To 5
Set Rng = Range(Cells(1, i), Cells(100, i))
If Application.WorksheetFunction.CountIf(Rng, c.Value) > 0 Then Exit For
Next i

Select Case i
Case 2
c.Font.ColorIndex = 3 'Red
Case 3
c.Font.ColorIndex = 5 'Blue
Case 4
c.Font.ColorIndex = 6 'Yellow
Case 5
c.Font.ColorIndex = 4 'Green
Case Else
c.Font.ColorIndex = xlAutomatic
End Select
Next c

End Sub``````

Is this pretty close to what you're looking to do?

Replies
1
Views
222
Replies
4
Views
641
Replies
3
Views
385
Replies
8
Views
892
Replies
2
Views
528

1,218,788
Messages
6,144,501
Members
450,549
Latest member
desperato

### 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.

### Which adblocker are you using?

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

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