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 Video Tutorials

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 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 "=

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


Hi I'm new to the forum and new to programming for excel. I am trying to organize a spreadsheet by filling in certain cells with certain colors.

First I want to find all the cells under the column titled "Code" (currently column B if that makes it easier) that contain "TI" (thats a capital i) and then in each of these rows color in the column titled "TN_Required_End" (currently column L) with the color green.

Next I want to find all the cells under the column titled "Type" (column F) that contain "D2" or "B3" and then in each of these rows check the column titled "TN_Planned_End" (column N) to see if it contains any characters (specifically to check if there is a date entered there). If there is a date, leave it alone. If there is no date, color it yellow.

Let me know if I need to clarify anything. Thank you


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


I don't have a lot of experience working with excel macros or VBA, but I understand how it works. Could anyone help me with this? It's is not complex, it just me because i dont know the syntax of VbA.
Here is what i need.

I have excel spreadsheet.

And A1:A10, is a TEXT for example A1="Blue..",A2="Green..",A3="Red..",A4="Red.."
And there is another column C1:C10, for example(numbers) C1="3.44",C2="4.55"....
And there are 3 cells, D1(blue),D2(green),D3(red).

I need something like this:

pseudo code

 For i = 1 to 10
	If Ai CONTAINS "Blue" 
	Then D1 + Ci
	Fill Bi with text "This is Blue"
	Else if	

	Ai CONTAINS "Green" 
	Then D2 + Ci
	Fill Bi with text "This is Green" 
	Else if	

	Then D3 + Ci
	Fill Bi with text "This is Green" 
	Fill Ai with RED color.

To make it even more clear, Ai = A + i = ex, A1,A2.. same with Ci, i is row number.

As you can see it is a very basic program, it basicly loops through the cells and if it finds what he is looking for it adds the number, which is in the same line it found it, fills with other text.

I don't except you to do it for me, but i would really appreaciate the example or a where to start.

Thank you

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.

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.

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","")

(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?

In EXCEL, I want to change the cell color to reflect the text value. Like if
the cell contains "John", I want the cell color to be green, if "Bob", the
cell color to be Blue, if "Frank", purple, if "Joe", red, etc. I want to be
able to display about 18 different colors. Conditional Formatting has a
limitation of three if conditions. Thanks for your help.


What I am trying to do is this. I have many columns but I want to look at 3 columns:


in column A, I want to look for the word "standard"
in column B, I want to look for the number "3"
in column C, i want to find any number greater than or equal to "3"

The end result would cell fill with the color *red* the cell in column C (if all three are found)

Not sure if a formula will work here or VB scripting (macro)

please 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 2 colums of data, A and B. Both columns contain text of either "yes" or "no". I need to format column B to fill color based on these parameters:

RED if A="yes" and B="no"
GREEN if A="no" and B="yes"

Help!? Thanks in advance.

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'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!

Just curious if I can Say Fill this Combobox with letters from A to AZ, so I do not need to do this:


Me.cobColumn.List = Array("A", "B", "C", "D", "E", "F", "G", "H", _
        "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", _
        "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", _
        "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", _
        "AY", "AZ")

Hello all,

Trying to write a macro that will fill a specific range of cells with a color if a condition is met.

In the attached file, in the "Before" tab and in the "OPENS" column, if the row contains the word "OPEN" in this column, then Rights, Rights2, and Rights3 are filled with gray (color index 15). See "After" tab.

The "OPENS" column can be in any column letter, which is why I'd like to use the header in row 1 to identify this column. Would need to loop through several thousand rows.

Help is most appreciated - thank you!

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?


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?

EDIT: Sorry, I have badly explained the following post. Please see #4 and #8 for clarification.
I don't know how to work around the 3 rules limit for conditional formatting in Excel XP (2002).

I have a table with text fields only.
I need 4 rules, e.g.

(1) if field content = "football" OR "baseball", set cell color to blue
(2) if field content = "tennis" OR "basket ball", set cell color to red
(3) if field content = "BMX" OR "snowboard", set cell color to green
(4) if field content = "judo" OR "karate", set cell color to yellow

What is the solution?
Thank you so much.


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