Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Conditional formating for entire rows [Solved/Closed]

Report
-
 Trowa -
Hello,

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):

Ma''Di''Wo'=Dayname
14''15''16''=day of the month
1''''''2'''''''3''''=colorcode

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.
See more

7 replies

Answer 1 / 7
Posts
7083
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
September 25, 2020
 475
Hi Trowa,

Hope your are well now. Here is the file, I loaded it on a french deposite file website because time is unlimited :
http://www.cijoint.fr/cjlink.php?file=cj200906/cijrcx9d3Z.xls
(The conditional formats are on the worksheet named "Januari" in cells C7 to AG7)
See you :)
4
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

Thanks Aquarelle,

You are the greatest!

Issue solved.
Answer 2 / 7
Posts
7083
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
September 25, 2020
 475
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 : http://translate.google.fr/translate?u=http%3A%2F%2Fwww.mdf-xlpages.com%2Fmodules%2Fsmartsection%2Fitem.php%3Fitemid%3D53&sl=fr&tl=en&hl=fr&ie=UTF-8

Now, here is your excel file with the macro, I just use it on the worksheet named "Januari" : https://authentification.site/783849333.html

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

Best regards
Answer 3 / 7
Posts
2
Registration date
Sunday March 29, 2009
Status
Member
Last seen
June 5, 2009
hi,
i guess you should use Macro to solve your problem.
try this link to get more information and free tutorials about it:
https://www.helpwithpcs.com/software/microsoft-excel-macro-tutorial.php
Answer 4 / 7

Thanks for your answer sinmatter, unfortunatly I couldn't find my answer in the link you posted.

Question is still open...
Answer 5 / 7
Posts
7083
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
September 25, 2020
 475
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 https://authentification.site 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.
Report

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:
https://authentification.site/654707797.html

Hoping I've provided all the info you need.
Best regards.
Answer 6 / 7
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
 307
Hi
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
Answer 7 / 7

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,
Trowa
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!