Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

Set Cell Fill Color Based On Multiple Conditions

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have a worksheet with several columns and 500 rows, I need a VBA script or formula that will check one of 3 conditions

the cells are B3 H3 N3 the key cell is N3

1- if N3 contains the word ball then check the conditions of B3 and H3 and proceed to set 2 and 3. if not, then do nothing

2- If N3 contains the word "ball" and B3 contains "P1" and H3 is "=<2:00:00" hours then set H3 cell fill color to green; but if H3 is >2:00:00 set H3 fill color to Red

or if cell B3 contains "P2"

3- If N3 contains the word "ball" and B3 contains "P2" and H3 is "=<6:00:00" hours then set H3 cell fill color to green; but if H3 is >6:00:00 set H3 fill color to Red

once it check row 3 I need it to then check all the rows in the sheet and run the code as required

View Answers     

Similar Excel Tutorials

VBA IF Statement in Excel Macros
IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement gives you the power to ...
Logical Operators in Excel VBA Macros
Logical operators in VBA allow you to make decisions when certain conditions are met. They allow you to check if s ...
Sparkling Christmas Tree in Excel
How to make a wonderful sparkling Christmas tree in Excel. This method uses only formulas; there is no VBA or Macro ...
Make a Transparent Chart in Excel
How to make a Chart transparent so that it blends in with its background and surroundings in Excel. Hover your mous ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics

I`m making a work were I need to fill a range of cells with a list of values. What I need to do now is the following:

If cell is filled with:
"Please select" the result must be a "white" color
"Not started" the result must be a "red" color
"Started" the result must be a "orange" color
"Started, final phase" the result must be a "yellow" color
"Finished" the result must be a "Green" color
"Not applicable" the result must be a "light green" color

With Conditional Formating I`m limit to 3 options.
Can anyone help me with this topic??
Thanks in advance!

I am terrible at sheet formulas. I am trying to check the value of one cell (M1) and if it is "Y" then another cell (A1) needs to have a fill color applied, preferably ".ThemeColor = xlThemeColorDark2" and ".TintAndShade = -0.249977111117893".
If the cell is not "Y" then no fill color.

This is basic VBA.

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With

Is this best done via VBA?

I'm stuck, can someone provide the "How to" for this ?


I have 3-cells merged; being A1,B1, and C1.

I have put two (2) "Check Boxes" from the 'forms' menu inside these merged cells(and hopefully on the foreground), with one CheckBox on the left, and the other on the right. The Check Boxes are to represent "YES" or "NO".


I would like the fill-color of the merged cells to be 'red' if NEITHER box is checked, OR, if BOTH are checked. Lastly, if ONLY ONE box is check, the merged cell fill color is to be green.

Lastly, I do NOT want to see the 'text' status of the check box as TRUE or FALSE.

NOTE: I am hoping that this can be accomplished without the need for a macro.

Thanks in advance to all you smart people :-)

Jay in Calif

I have used Conditional Formatting to fill a cell's background with a certain color based on a specific value. I would like to fill adjacent cells automatically with whatever color the target cell gets, by using Interior.ColorIndex. Problem is, it appears that the color applied to the target cell is some hard-coded value, not a true color from the index. For example (using yellow), the cell shows yellow, but if I say - Code:

Range("N4").Interior.ColorIndex = Range("J4").Interior.ColorIndex

, I get nothing in N4. When I go to J4 (where I see yellow), I try to make it "No Fill" for color, just to test it, but I still get yellow. The no fill from the palette has no effect. It is as though, even though I see yellow, it is not coming from the pallette, so it does not transfer the color over using interior.colorIndex. Does Conditional formatting somehow "fake" the color? is there a way around this?

Thanks much in advance


I am a VBA novice. Using the basic color palette in Excel (note: my business still uses 2003), I want the fill color of a cell to change based on the name of the color input into the cell. For instance, if "red" is typed in A1, the fill color of A1 turns to red. If "yellow" is typed in B1, the fill color of B1 turns to yellow. In addition, other pages in my notebook will reference these cells with a simple equal function and those cells also need to change to the correct fill color.

I have searched the internet for two days and cannot find an answer. Help would be greatly appreciated.

(Windows XP & Excel 2003)
Im having huge trubles with the interior color for the cells.
I have a macro that removes the color of a cell ("No fill").
For that purpose, I use the line:


In another Macro I check the the color of the cell and if it has No fill; then another procedure must run.

Strange is that now if I go to Excel toolbar and click in the color bucket and select "No Fill"; the macro that reads the colorindex reads now -4142. (Before it was reading -4105).

Is there any scientific explanation for that? Wich color is the right one?


this is very urgent.. please help..

I have a set of formulas which have to be placed in loop for making conditional formmating,
=IF(AND(AN$7<$W9, AN$7>=$V9),"Yellow"," " )
=IF(AND(AN$7<$X9, AN$7>=$W9),"LB"," ")
= IF(AND(AN$7<$Y9, AN$7>=$X9), "Violet"," ")
=IF(AND(AN$7<$Z9,AN$7>=$Y9), "Green"," ")
=IF(AND(AO$7>$AB9,AN$7<=$AB9), "amber"," ")
=IF(AND(AO$7>$AB9, AN$7<=$AB9), "LB Lines"," ")
=IF(AND(AO$7>$AD9, AN$7<=$AD9), "green cross"," ")
=IF(AND(AO$7>$AF9, AN$7<=$AF9), " green st lines "," ")
=IF(AND(AO$7>$AG9, AN$7<=$AG9), " green checks "," ")
=IF(AND(AO$7>$AC9,AN$7<=$AC9), " purple cross "," ")

let me explain, if the first line is true then it must display yellow color, else need to check the second row and if that is true it must display light blue color, silimilarly must check each and every condition and display respective color based on the true value, else must display blank ..

the AN and AO have weekly dates..19, 26,2 etc..
X,Y,Z.. have the dates of the project phase...

I am eventually working to get a Gantt view....

Please help ASAP

Please refer attached workbook..

Say I have a word "interaction" in Cell B1.
I fill up the word "cat", "rat", "inini", "caat", "ticon" in the Cell A2,A3,A4,A5 and A6.
I need to create a formula to check if the word in the Cell A2, A3,A4.A5 and A6 can be created using characters available in Cell B1. Say, if only 2 "i" is available in Cel B1, we cannot create word word 3 "i".
I need a formula to be entered from Cell B2,B3,B4,B5 and B6 to check the constructibility of the words in the Column A.
Idf the words are constructible, the words appear in Colum B, in not, "x" appears.

Appreciate if anyone can help.

My question is this, I have a spreadsheet where I have cells that utilize conditional formatting in that if a certain cell is filled with text it will color a certain cell green. The colored cell is using [=ISTEXT(F29)] as the condition. Now the one thing I'd like to do is if the cell, for example, F29 has text in it it will color cell B13 green, but if the text in cell F29 is just the word "SPARE" it will fill cell B13 the color orange. How would I go about doing this? I've tried using conditional formatting and having 2 seperate conditions but can't seem to figure it out, no matter what text I put in the cell it will color it green.

I hope this question is clear enough



Hi all

I have an excel sheet where i ned to fill the attendence. My aim is to make a cell to fill with green if the value of the cell is either "P" or "PA" or "PB" and it should be red if the cell value is "L". I couldn't do it through conditional formating as i have four conditions. Pls help me in doing it.

Hi All,

I'm formatting row color based on the value of a cell within that. I have now so that if column "I" has a value of "W," the row is formatted green. However, I only want rows that have both "W" in column I and "FIA" in column H.

The formula I have is:
="($I6=""W"" AND $H6=""FIA"")"

But that's not working. Any suggestions?

Thanks in advance.

I have this huge formula that I am using to create a field with text in it defining ingredients in food items. Is there anyway I can break it up?

Here's the formula:

=C2&IF(SUM(D2:E2)>0," -","")
&IF(D2=3," on request","")
&IF(SUM(D2:E2)=2," or ","")
&IF(E2=3," on request","")
&IF(SUM(D2:E2)=2," on request","")
&IF(SUM(F2:O2)>0," contains (","")
&IF(P2=1," May Contain","")
&IF(AND(G2=1,H2=0,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(Q2=1," May Contain","")
&IF(G2>0," Dairy","")
&IF(AND(H2=1,I2=0,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(R2=1," May Contain","")
&IF(H2>0," Eggs","")
&IF(AND(I2=1,J2=0,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(S2=1," May Contain","")
&IF(I2>0," Gluten","")
&IF(AND(J2=1,K2=0,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(T2=1," May Contain","")
&IF(J2>0," Soy","")
&IF(AND(K2=1,L2=0,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(U2=1," May Contain","")
&IF(K2>0," Peanuts","")
&IF(AND(L2=1,M2=0,N2=0,O2=0,SUM(F2:O2)>1)," &","")
&IF(V2=1," May Contain","")
&IF(L2>0," Nuts","")
&IF(AND(M2=1,N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(W2=1," May Contain","")
&IF(M2>0," Seeds","")
&IF(AND(N2=1,O2=0,SUM(F2:O2)>1)," &","")
&IF(X2=1," May Contain","")
&IF(N2>0," Fish","")
&IF(AND(O2=1,SUM(F2:O2)>1)," &","")
&IF(Y2=1," May Contain","")
&IF(O2>0," Shell Fish","")

I would like to add the following VBA to my macro: Have it go down column D and if the word "Dog" is found, color fill that cell and all the ones to the right in that row until no data. The last cell contains the word "Total". So if D15 has "Dog" and Total is in D20 than D15 through D20 will be colored Blue. Thanks for help.

is there another way to get what I'm after.

I am currently using conditional formatting to change colors of cells.

Here is the typical formula.

Condition 1 - Formula is =$E$6="Red" then this cell (F6) will fill with red
Condition 2 - Formula is =$E$6="Blue" then this cell (F6) will fill with blue
Condition 3 - Formula is =$E$6="Green" then this cell (F6) will fill with green

The formula is basically saying:
if cell E6 text reads Red, then fill cell F6 with the color Red.

The problem is, I have Yellow and Magenta that I also need to use but it looks as though Excel is limited to 3 conditions. Is there another way I can do this so I can use 5 colors or more if needed?

I know Excel doesn't read fill color but can you make a macro that finds all
cells with the word "total" in it and fill color that entire row?

Thanks in advance for any help!

heres what i want to do...

assign/fill cell b2 with a specific color
assign/fill cell d2 with a different color
assign/fill cell f2 with another

select a group of cells (b4-b7) and while selected, be able to then click cell b2 and the color of the selected cells will change to whatever color i have designated in cell b2.

i am trying to make a user friendly type of timeline spreadsheet where the user can just select a group of dates along the timeline and color them without having to look for the matching color in the color picker. i will have red, green, blue, and no fill, so if the dates change they can "erase" them with no fill.

in essance the colored cells would just be shortcut "buttons" they would not have any editable type or function to them

make sense? i just need to know the process, but a sample code (if necissary) would be appriciated


Can any one help me to solve the problem

Please open the attached file and read the below statements.

if "A1" Cell color is ="Red", "A1" values * A8
if "A1" Cell color is ="Yellow", "A1" values * A9
if "A1" Cell color is ="Green", "A1" values * A10

when the color is changed in the range of "A1:D3" that respective cell value * Standard values for Colors ("A8 to A10")

for more details see the out put from "A16:D18 in the attached file.

Hope it is clear

Best Regards


I am looking for a VBA with multiple message boxes based on following criteria:

1) Check if specific cells are blank. If blank --> Message Box " Mandatory fields still blank - please fill cells"

2) Check all other cells if there are blanks. If there are blanks--> Message Box "There are still empty cells - Proceed?"

3) If both boxes have been passed, then Message Box "Control Check Complete"

I found this VBA online which really helped - but i'm not sure how to add the other two conditions to make it work - any help please??? Thanks!!

Sub FormatCheck()

Dim CheckCell As Range

For Each CheckCell In Sheets("FeederSheet").Range("E9,E15,E16,E18,E19").Cells
If Len(Trim(CheckCell.Value)) = 0 Then
MsgBox "Cell " & CheckCell.Address(0, 0) & " is a Mandatory Field. Click OK and fill the cell.", , "Missing Information"

Exit Sub
End If
Next CheckCell

I would like the macro to go down the sheet and if the value in the cell is "Greg" then color fill all the cells blue and Font color white the active cells in that row. All of these will be in column D(thats where text starts in all rows). So if D12 has "Greg" in it than it will color fill blue & font color white all the cells from "Greg" right until it gets to a cell the is "".How can I write this in VBA. Thanks for help.

If I put a "W" anywhere in cells B4:B150.........i want it to fill that row with a certain color of fill on that row. For example. If i put a "W" in cell B9 I want it to fill E9:H9. If I put a "W" in cell B21 i want it to fill E21:H21. I hope that makes sense...

hi, my sample worksheet attached & i want to fill empty cells using above cell data . first sheet is normal data & second sheet is expecting result. there are two conditions.

01. column "K" i don't want to fill.
02. filling should base on column "D". i mean if column "D" 4 rows fill then other columns also need to fill only 4 rows. pls see bellow image.

i have one macro for this. only the problem is it's filling column "K" also. i don't want to fill column "K" other than everything work fine.. so i need macro for this. if possible pls edit my bellow macro. if not pls make new one for me. thanks a in advance..

Please Login or Register  to view this content.

what i am trying to do is toggle the color of a shape between red and green. what i have currently (courtesy of a search on this site) is this macro:


Sub Oval2_Click()
If ActiveSheet.Shapes("Oval 2").Fill.Visible = msoFalse Then
ActiveSheet.Shapes("Oval 2").Fill.Visible = msoTrue
ActiveSheet.Shapes("Oval 2").Fill.Visible = msoFalse
End If
End Sub

it works great when attached to the shape but the only problem is it toggles between no-fill and white. i am assuming i would change it to something along the lines of this:


Sub ShapeClick()
If ActiveSheet.Shapes("Oval 3").Fill.ColorInex = Red Then
ActiveSheet.Shapes("Oval 3").Fill.ColorIndex = Green
ActiveSheet.Shapes("Oval 3").Fill.ColorIndex = Red
End If
End Sub

the only problem is i haven't worked with macros in excel before so i don't know what the actual code is for setting it to a color so it obviously won't work.

any help would be very much appreciated, thanks.

PS also i am going to need to do the same thing but instead of filling with a color i am going to need the fill for a shape to toggle between two pictures. Not 100% sure that will be possible though since no everyone that has the excel file will have those files on their computer in the same spot but thought there might be some way to attach them to the excel template.

please check this attached file and let me knw what i did mistake in Conditional Formatting

=E2="we have more than 90 days" i like to fill green colors in e2 cell

=E2="we have less than 90 days" i like to fill red color in e2 cell

but its only showing green and white..

plz let me knw

In need help. The conditional formatting I want goes in cells A1 to A10 and
also M1 to M10.

The conditions in cell A1 is:
If the text in cell D1 is "ant" the cell color in A1 should be Yellow
If the text in cell D1 is "noa" the cell color in A1 should be Pale Blue
If the text in cell D1 is "nop" the cell color in A1 should be Tan
If the text in cell D1 is "vac" the cell color in A1 should be Pink
If the text in cell D1 is "req" the cell color in A1 should be Bright Green
If the text in cell D1 is "off" the color in A1 should be Plum

The conditional formatting goes in cells A1 to A10 and M1 to M10 with each
line refering to the cell on the same line in column D, i.e., A6 looks at
D6. M4 looks at P4.

How do I do this?

Hi, I was wondering if I could get help with a VBA project I'm working on to write a macro script that will check the string within a cell to see if conditions are met and then color the cell if any of them are met.

I have some programming experience, but I don't know the syntax of VBA very well. I'm having difficulty figuring out how to activate cells, shift down to test the next cell, etc.

There are 10 conditions, mainly of 3 types.
1) Is there a "Q" in the string
2) Are there multiple "C"s in the string
3) Are there more than 20 characters (ignore spaces) in the string

Essentially the macro will select cell "i2", test the cell text (containing a string of capital letters and spaces) for the 10 conditions, and color the cell background red if any conditions are met. Then it will check i3, i4, i5,...i500. Then it will move to k2, k3, k4....k500. M...O...(increment by 2 letters) until AC2...AC500.

My work so far:

I have a variable defined for each letter (ex: Ccount), a total length variable for checking total length, and an errorcount variable will increment from 0 if any conditions are met, and the cell will turn red if errorcount is >0 at the end of the cycle for each cell. Then it should set everything back to 0 and redo the process.

If someone could help with the outlining for the first 3 conditions, I can implement the others since they're very similar.

I already have the functions written out for each condition, but since the program will be checking hundreds of cells for all 10, I figure a loop in a macro in VBA would be more elegant. However, if there are suggestions for using my current functions (lots of len() ) those might be useful instead.