Conditional Formatting Using Color

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
873
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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
873
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,456
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
873
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,456
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,127
Messages
5,857,525
Members
431,883
Latest member
Hien

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
Top