creating conditional formatting rules

[enedinamaddison]

I have been looking everywhere online on how to create my own formatting rules in excel 2010. I understand that rules start with a equals sign and then parenthesis but, after that what next. For instance how does one know where to put the quotation marks and the semi colin and the ampersand sign. One tutorial explained that if one wanted a text cell rather than a numeric cell, to add a apostrophe to the cell then type the numbers. He stated this would store the number as text and not number. How did he know to add the apostrophe. In other words where to learn how create rules manually instead of the pre-inserted ones excell gives us.

[judgeh59]

conditional formatting is based on TRUE/FALSE.....if it's TRUE Then make the background RED....so, a lot of the time I'll test my T/F in the column next to the one I want to apply the CF....
so, if I want the BG Color to be RED if this cell is greater than that cell, in my test cell I'll type.....=A2>A3.....and if I get a TRUE and that's I want, I type that into the CF formula area....all of the semicolon and & stuff is in most of the help stuff....and researching and site like this....HTH

[FDibbins]

Hi and welcome to the forum

If you want to apply Conditional Formatting (CF) to a particular cell, based on the contants of THAT cell, then you can often use the built-in rules in the CF menu - format cells based on value...format cells that contain...etc etc.

However, if you want to apply CF to a cell (or range of cells) based on the contents of another cell, then you need to use the "use formula" to create you'r own rule.

These rules that you create are nothing more or less than regular excel formulas that are constructed in such a way as to return a TRUE or FALSE answer (as mentioned by Judgeh, above). So for instance if you want to test to see if the sum of a range of cells (A5:A10) is = to a certain value (50), then your formula would be constructed to test for that...=sum(A5:A10)=50

That is obviously a very simplified example, but it shows the principle.

With regards to "For instance how does one know where to put the quotation marks and the semi colin and the ampersand sign." if they would be used in a regular formula that you would use for the test, then they would be used in the same manner - if they would not be used in your formula, they dont get used.

Quotation marks are generally used to define text in a formula
I cant think off-hand when you would use a semi colin, but a comma , would be used to seperate formulas/functions or arguments withing a function =vlookup(find this,inthis range, in this column, false)
You would use an ampersand sign to join answers and/or text =sum(A5:A10)&" total widgets made"

Hope that explains a bit more for you? Feel free to keep asking, thats what we are here for

[enedinamaddison]

thanks for answering, so lets say i wanted a questionaire made. if i wanted the word name in red in one cell a1, then when the person enters their name in cell a2, i would like for cell a1 to turn black. i have tried different things but none of them work. maybe its program for that? idk. lol...

[FDibbins]

If I inderstand you correctly...

When you enter "Mike" into A2, you want A1 to change from red to black?

1. highlight the range you want to apply the conditional formatting to (a1)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =isblank(A1) fornat fill black
5. select new rule, select use formula
6. enter =not(isblank(A1)) fornat fill red

instead of =isblank(A1) you could use =A1=""
instead of =not(isblank(A1)) you could use =A1<""

This will make A1 red if A2 is blank, and change it to black if there is anything - at all - in A2
Hope that helps?

[enedinamaddison]

oh wow.... lol. i was far from figuring it out. well thank you so much. im sorry to be so much trouble. maybe i should just take a class on this. thank you for your help

[FDibbins]

No trouble at all, Im happy to help and thanks for the feedback. I look forward to your next question

All you have to do, if figure out how you would do the test in a regular worksheet. Once you have that down, te easy part is putting it into CF