Conditional Formatting Using Color

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Thanks for your help!!

Steve
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Conditional Formatting

The numbers are entered manually.

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

Thanks for all your help!!

Steve
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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