Conditional formating for entire rows

Trowa - Jun 5, 2009 at 07:03 AM
 Trowa - Jun 30, 2009 at 07:24 AM

First I would like to thank Aquarelle for her kind words, regarding asking questions.

I'm working on a calender where each day has a different color according to the dayname. When I alter the worksheet for next year, each date gets a different dayname. Since I also implemented holidays into the calender I've given each day a colorcode: 1=monday, 2=tuesday, 3=wednesday, 4=thursday, 5=friday, 6=weekend, 7=holiday.

Now I've put the days in columns and the colorcode in a row. So it looks like this (I'm using ' for spacing):

14''15''16''=day of the month

Below this I've put names, so I can input their free days.

Now I want to change the color of the entire column according to the colorcode. This works great with conditional formating; Select entire row, condition: formula is ... etc.. But as known, conditional formating only has three conditions.

I've found lots of answers about conditional formating with more than three conditions, But I can't find an answer for coloring rows according to the condition of 1 cell.

I hope I've clearified my issue well enough.

Best regards.

7 responses

aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Jun 29, 2009 at 11:30 AM
Hi Trowa,

Hope your are well now. Here is the file, I loaded it on a french deposite file website because time is unlimited :
(The conditional formats are on the worksheet named "Januari" in cells C7 to AG7)
See you :)
Thanks Aquarelle,

You are the greatest!

Issue solved.
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Jun 11, 2009 at 03:34 PM
Hi Trowa,

I found a way to do what you want with a macro which creates Conditional formatting with unlimited criteria .
I found it on a french website where you will find explanations, here is the link translated into English by Google :

Now, here is your excel file with the macro, I just use it on the worksheet named "Januari" :

If you have some questions, do not hesitate, I will try to help you.

Best regards
sinmatter Posts 2 Registration date Sunday March 29, 2009 Status Member Last seen June 5, 2009
Jun 5, 2009 at 08:34 AM
i guess you should use Macro to solve your problem.
try this link to get more information and free tutorials about it:
Thanks for your answer sinmatter, unfortunatly I couldn't find my answer in the link you posted.

Question is still open...

Didn't find the answer you are looking for?

Ask a question
aquarelle Posts 7140 Registration date Saturday April 7, 2007 Status Moderator Last seen March 25, 2024 491
Jun 8, 2009 at 02:32 PM
Hello Trowan,
Happy to see you coming back here. Please could you load a brief example of your excel file (without any private or personal data) on then copy past the created link to your next answer.
Because to create a macro, I need to know where and how are organised the different data.
See you soon.
Hi aquarelle,

Happy to see you're willing to help me once again.

Just to be complete, I've uploaded the entire file (without names).

I was asked to create a file to register the vacationdays of the company employees.
Now I'm trying to prepare the file for the years to come.

Unfortunatly the file is in dutch, so you might not understand everything.
Take a look at the worksheet named "Januari".
The colorcodes are in row 7. To keep the sheet clear, the textcolor is white.

So according to the colorcode in C7, I want to color C5:C6, C8:C200.
And according to the colorcode in D7, I want to color D5:D6, D8:D200, and so on.
I've already looked up the colornumber used in excel, which can be found in worksheet "Gegevensblad", starting in cell U2.

Here is the link:

Hoping I've provided all the info you need.
Best regards.
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Jun 9, 2009 at 07:50 AM
To conditional format an entire row based on a single cell's content lock the cell (eg $A23) in the condition and the range to apply select the entire row
Thanks again for your time Aquarelle.

Ufortunatly I've been ill for the past week and a half and now I can't download the file from speedyshare anymore.

I've tried to copy the code for myself with the link you've provided, but apperently I'm forgetting something since it's not working for me.

Could you upload the file once more?

If you keep helping me like this, be sure to see me again. :)

Best regards,