Conditional formating for entire rowsSolved/Closed

Question

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.

aquarelle · Accepted Answer

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

sinmatter · Answer

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

Trowa · Answer

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

aquarelle · Answer

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.

Excelguru · Answer

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

aquarelle · Answer

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

Trowa · Answer

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

Conditional formating for entire rows

7 replies

Subscribe To Our Newsletter!