Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

Remove Gridlines from Only Part of a Worksheet in Excel
How to remove gridlines from a part of a worksheet in Excel instead of the entire worksheet or workbook. This is i ...
AutoFilter with an Excel Macro
This Excel tip shows you how you can use an Excel Macro in order to run a Filter on data within a spreadsheet. Filt ...
How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as well as adding text to t ...
How to Add Formatting to Cells and Data in Excel Styles, Fonts, Colors, & More
In this tutorial I will cover how to use the various formatting tools in Excel. The Formatting tools are located on ...

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




I have a sheet with a list of data in column A, and a list of data in column B.
I want to accentuate (or extract) the differences between those two rows and put those in a new column.
(and possibly have a new column with A and B added together without duplicates)

The complicating factor (which makes it impossible for me to know where to start solving this problem), is that only a specified string in these cells is to be checked for differences/similarities.

To be more specific:
I want to have cell content [ <color="black" "123" ] being considered to be a match with [ <color="black" "abc" ]
So only the specific string between [ < ] and the second [ " ] is to be checked for difference/similarity.

So something like this:

input input result result result A B missing from A missing from B A + B <color="black" "123" <color="black" "abc" <color="blue" "bcd" <color="green" "234" <color="black" "123" <color="green" "234" <color="blue" "bcd" <color="yellow" "def" <color="red" "345" <color="blue" "bcd" <color="red" "345" <color="white" "cde" <color="green" "234" <color="white" "456" <color="yellow" "def" <color="red" "345" <color="white" "456" <color="yellow" "def"

I attached an excel sheet to hopefully make it easier to understand (and test) what I would like to achieve.

extract column differences.xlsx

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.

I'm using an IF function to call some other cells, but I want to have the various words color coded, eg ("Certified" is green, "Recertify" is yellow, and "Not Certified" is red). I have the IF function working correctly, but when it "calls" the cell to have it display the appropriate word, the color of each word is not brought along with it...only the text. Is there a way to be able to color code it so that the appropriate color appears? Thanks!

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 a rather large file with only two columns that I imported from an old Xbase database with a number in the first column and colors (associated with that number in the second.

"A number" Color 1
Color 2
Color 3

"Next number" Color 1
Color 2
Color 3

I need to transpose the colors to be in a row next to the numbers and then change the fill in the color cells based on the text (color) within the cell.

Any ideas?


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

Hey Guys,

Is it possible to format a rows font color based on 3 conditions and 3 colors and do it for the entire sheet?
There are 8 colums of data and the format should be applied to column "I"


If a cell in column "I" = "Blah Blah" then that rows font color is Red
If a cell in column "I" = "Hee Haa" then that rows font color is Purple

Last one is just a tad different

If a cell in column "E" is greater than 10,000 then that rows font color is Blue

I have searched and tried this myself all weekend but i cant get it. Thanks for any help.


I was wondering if there was a way to auto fill formula where several cells change but one stays the same (see example below)

=IF(AND(G12="YES",D12<A2,F12="YES"),"CHECK NEEDED","")
=IF(AND(G13="YES",D13<A2,F13="YES"),"CHECK NEEDED","")
=IF(AND(G14="YES",D14<A2,F14="YES"),"CHECK NEEDED","")

i need the part that refers to A2 to stay the same and the rest to increase with the row. I know I could change it manually but basically i am to lazy so I thought i would ask.



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