Search : in
By :

Conditional formating for entire rows

Last answer on Jun 30, 2009 12:24:49 pm BST Trowa, on Jun 5, 2009 12:03:34 pm BST 
 Report this message to moderators

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.

Configuration: Windows 2003 Internet Explorer 7.0

Best answers for « Conditional formating for entire rows » in :
Unlimited Conditional Formatting Show Unlimited Conditional Formatting Introduction Principle Code Introduction The limitation of MFC (Conditional Formatting) to Excel version 2007 is less than 3. This is very short. Having seen many applications to push the limit, I am...
Conditional formating - apply on more cells Show
Conditional Format - More than 3 colours Show
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
[VBA] A function that returns the color of an active MFC Show[VBA] A function that returns the color of an active MFC This function returns the value of the active format in conditional formatting. With the function below, two values can be returned. Mode = 0: the value of Interior.ColorIndex...
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
Download HP USB Disk Storage Format Tool ShowHP USB Disk Storage Format Tool is a small utility allowing to format a key USB or READER MP4. It also allows to make a key (Bootable). This software works with all keys and readers MP4 of the market. To make work this utility under Windows...
Download HDD Low Level Format Tool Show
Download Format Factory ShowFormat Factory is an application that allows conversion of various types of media files. It has various characteristics that allow you to customize a lot of stuff like: Convert video formats, audio and picture shows. Repair damaged files. Reduce...
General Terms and Conditions of Use ShowAll Rights Reserved - 2009 - Communitic International Object These General Terms and Conditions are meant to define the ways in which the services of the website en.kioskea.net, hereafter "The Service," are made available, as well as the...
MKV format (Matroska Video) ShowMKV format The MKV format (Matroska Video) is an entirely free video format. More precisely, it is a container (hence the name Matroska, in reference to the Russian dolls contained within another) making it possible to contain video (DivX, Xvid,...
Formatting - Formatting a hard drive ShowBefore trying to understand formatting, you first need to understand how a hard drive works. Many people do not distinguish low-level formatting (also called physical formatting) from high-level formatting (also called logical formatting). Even...
Ask your question Reply

1

sinmatter, on Jun 5, 2009 1:34:49 pm BST

Hi,
i guess you should use Macro to solve your problem.
try this link to get more information and free tutorials about it:
http://www.helpwithpcs.com/tipsandtricks/microsoft-excel-mac­ro-tutorial.htm

   
Reply to sinmatter

2

Trowa, on Jun 8, 2009 12:33:02 pm BST

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

Question is still open...

   
Reply to Trowa

3

aquarelle, on Jun 8, 2009 7:32:58 pm BST

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 http://www.speedyshare.com/ 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. "Pour trouver une solution à ses problèmes, il faut s'en donner la peine."

   
Reply to aquarelle

4

Trowa, on Jun 9, 2009 11:38:57 am BST

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:
http://www.speedyshare.com/654707797.html

Hoping I've provided all the info you need.
Best regards.

   
Reply to Trowa

5

Excelguru, on Jun 9, 2009 12:50:51 pm BST

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 Winners are losers who got up and gave it one more try. -Den­nis DeYoung
My Interests are financial Modelling and custom excel develo­pment.

   
Reply to Excelguru

6

aquarelle, on Jun 11, 2009 8:34:04 pm BST
  • +2

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" : http://www.speedyshare.com/783849333.html

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

Best regards "Pour trouver une solution à ses problèmes, il faut s'en donner la peine."

   
Reply to aquarelle

7

Trowa, on Jun 29, 2009 1:32:02 pm BST

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

   
Reply to Trowa

8

aquarelle, on Jun 29, 2009 4:30:29 pm BST
  • +3

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.xl­s
(The conditional formats are on the worksheet named "Januari" in cells C7 to AG7)
See you :)
"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."

   
Reply to aquarelle

9

 Trowa, on Jun 30, 2009 12:24:49 pm BST

Thanks Aquarelle,

You are the greatest!

Issue solved.

   
Reply to Trowa
Ask your question Reply
They need your help