• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting

A

Ashish Batham

Member
I have a roster for my team members which is created on a monthly basis in an excel. I have put a formula wherein if I enter the first date of the month, remaining dates are automatically populated horizontally. Above all the dates the "day" is also populated automatically.
For Saturday & Sunday I changes the color the first 10 cells of the column to grey (example) manually.
Please advice, if we have a macro wherein if the Sat or Sun is polulated in cell (say A1), then color of A1:A10 cell changes to grey
 
Hi Ashish,

Assuming the dates in column B & Days in C use:

Code: 
=OR(C1="Saturday",C1="Sunday")

..in conditional formatting using formula. or see this file. [XL2010] Hope that helps.
 

Attachments

  • CF_Highlight Sundays.xlsx
    9.5 KB · Views: 14
Thanks for the you help, however I want the cells to be highlighted from cell C2 to M2 (for example) when the cell C2 contains "Saturday".
Similarlly, when "Sunday" is entered in Cell C3 the cells till M3 should be colored
Please advice,
 
Good day Ashish Batham

What about the cells between C2 and M2 you say you want all between C2 and M2 but only talk about column M picking up CF.
Do you want all cells between C2 and M2 to have a colour depending on whether it is a Saturday or Sunday in C2? and this CF to be applied in any row of column C
 
Wow...!! It's amazing

Can this be done vertically as well. I mean, for example if "Saturday" or "Sunday" is entered in Cell A1 then color of Cells A1 to A10 changes?

Have a nice day,
Thanks & Regards,
Ashish
 
Ashish

Not sure why you want A1:A10 to be coloured if there is Sunday/Saturday name in A1, going to get a bit messy with all these Saturdays and Sundays all over and whole blocks of vertical cells coloured by the two day names whether they be Monday through to Friday!
 
Thanks, however only one cell in getting colored. I want the A1 to A10 cells to get colored, if Saturday or Sunday is entered in A1 (for example)
 
Ashish
In your first post you asked
For Saturday & Sunday I changes the color the first 10 cells of the column to grey (example) manually.
Please advice, if we have a macro wherein if the Sat or Sun is polulated in cell (say A1), then color of A1:A10 cell changes to grey
Click to expand...
and Faseeh responded. And you thanked him.

Then in your third post you asked
Thanks for the you help, however I want the cells to be highlighted from cell C2 to M2 (for example) when the cell C2 contains "Saturday".
Similarlly, when "Sunday" is entered in Cell C3 the cells till M3 should be colored
Click to expand...
and I responded and in your fifth post you responded with
Wow...!! It's amazing
Can this be done vertically as well. I mean, for example if "Saturday" or "Sunday" is entered in Cell A1 then color of Cells A1 to A10 changes?
Click to expand...
and I responded with an update.

Then in your seventh post you asked
No Sir, I meant instead of Horizontal I want the following table in the vertical manner & color coding to be only verticaly
Click to expand...
and I responded

Then in your eighth post you asked
Thanks...Sir attached is the file on which I need your help.
need to color column vertically.
Click to expand...
So just where does the HORIZONTAL A1:A10 fit in to the VERTICAL?

In your last post you said
Thanks, however only one cell in getting colored. I want the A1 to A10 cells to get colored, if Saturday or Sunday is entered in A1 (for example)
Click to expand...
.

You change your mind more times than my wife does when choosing a new dress!!!!

Have you actual gone to CF and opened up the Manage Rules and looked at what is happening. I can assure you there in are plenty of examples to look at.
 
Hi Sir,
I do understood how to color the cell automatically if the cell value is "Saturday" or "Sunday", but not able to understand that, if the Cell value is "Saturday" in A1, they how do i color cells A1 to A10 automatically with help of conditional formatting
 
Ashish,
Go to Conditional Formatting, Manage Rules. From dropdown, choose "This worksheet".
Delete Rule #2
Edit Rule #1
Formula should be:
=OR(A$2="Sat",A$2="Sun")
Click Ok
Change the "Applies to" range to be:
=$A$2:$N$10
Ok out.
 
Good day Luke M

It is a habit of my to put cell colour CF as single formulas so the when "Upper Management" change there minds as they do I find it easier to change to single formula, just habit.
 
Hi Bob,
No worries. It was just easier to explain having to change one formula rather than several. Of course, could get even shorter and just make the CF formula:
=WEEKDAY(A$1,2)>5
:)
 
Luke M

Your last formula is where when used the management would come along asking for all Sundays to be a different colour than Saturday
 
You must log in or register to reply here.
Back
Top