right formula to include it in conditional format when I need to include many criterias

[MGadAllah]

Hi,
Please I would like to add a new conditional format with a custom formula.
I choose "use a formula to determine which cells to format" to insert the formula.
I've 30 values separated into 2 sets, each set is 15.
(S1-01, S1-02 ..., S1-15)
(S2-01, S2-02 ..., S2-15)
I add a drop down list contains the 30 values.
I want to add a formula in conditional format ... I want it to do the following:-
If I choose any value of the 1st set (S1-01, S1-02 ..., S1-15), I want cell color to be filled with green color and font color white.
If I choose any value of the 2nd set (S2-01, S2-02 ..., S2-15), I want cell color to be filled with blue color and font color white.
So please what is the right formula?

[Fotis1991]

Hi

Higilight your cell-range. Let's say A2. Then in CF rules uses these formulaw(eatch one for eatch condition).

Please Login or Register  to view this content.

Please Login or Register  to view this content.

How can you use Conditional Formating.

--In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu. See here how you can work using CF.

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.

where A1 is the drop down cell.

[MGadAllah]

Thanks a lot for the fast reply.
Please note that in each cell in the range I will choose a one value only of the 30 values existing in the drop down list I've created.
Please check this example in this document
https://www.dropbox.com/s/3iryky9kyu...rmat.xlsx?dl=0

conditional format.xlsx

[sktneer]

Make New Rules for conditional formatting using the below formulas.....

For Green

Please Login or Register  to view this content.

For Blue

Please Login or Register  to view this content.

Is this what you are trying to achieve?

[Fotis1991]

Sample of what we suggested.

[MGadAllah]

Thanks a lot ... it seems that every one mentioned the same formula.
What does it means?
It does the job yes. Thanks a lot

[sktneer]

You're welcome.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

[MGadAllah]

What does it means? I mean regarding the formula exactly?

[Fotis1991]

if the 2 first(left(b1,2), letters-numbers, are S1 then gives you a true result so the cf condition highlight the cell.

[MGadAllah]

Agents absence & attendance schedule - final.xlsx
I am afraid that it did not worked the way I want and it seems that I've missed expressed the right meaning.
Attached is the real document so can get a better understanding.
Please work only on January sheet.
choose from drop down menu in any given day anything and try to apply the formula you mentioned earlier.
Thanks a lot

[sktneer]

On January Sheet, select the range B32:AF39 and use the following formula for conditional formatting.

For Green

Please Login or Register  to view this content.

For Blue

Please Login or Register  to view this content.

See the attached sheet with conditional formatting applied.

[MGadAllah]

But I want to apply it for the full whole range of days start from B5 till the end of AF39
Any way to do so?

[sktneer]

Oh really sorry. I didn't scroll up. See the attached sheet now.

[MGadAllah]

Yes this is the right way :D
Reputation added.
may I ask for another issue please :$
Please as you can see I am using a formula to determine the day of the week according to the formula in B4:AF4
Does there is any way or any work around so in case the day in the cell in that row or range, if it is Friday or Saturday to highlight its column with any color?

[sktneer]

Select the range B5:AF39 and use the following formula for conditional formatting.

Please Login or Register  to view this content.

Please find the attached sheet with conditional formatting applied. You can edit the formatting as per your choice. To do so, select the range again --> Conditional Formatting --> Manage Rules --> Select the correct rule from the list --> Edit --> Format --> Set the formatting as per your choice.

[MGadAllah]

Agents absence & attendance schedule - final (1).xlsx
This is not what I mean my friend.
I mean if the value in any cell in the range of B4:AF4 equal to friday or saturday, I want to make its column in the range of this column highlighted in another color.
I did it manually and want to do it with conditional format.

[sktneer]

In A2, I have input 1/1/2014 and custom formatted it as "mmmm" to show month name. Then Select the range B5:AF39 and use the following formula for conditional formatting....

Please Login or Register  to view this content.

See the attached sheet.

Is this what you are trying to achieve?