Conditional Formatting Using Color

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
796
Office Version
  1. 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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
 

ststern45

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
 

ststern45

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

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,401
Messages
5,547,740
Members
410,810
Latest member
thepinkbird
Top