I want to configure automatic formatting for my Excel datasheet.

Here's have an example (not mine):

excel datasheet

How can I automatically format the rows depending on one of its cell's values which is not a number and without writing a lot of code?

I saw "Conditional Formatting" with scale colors in Excel 2007. I would like something like that: each text value is binded to one color. Something like in cartography with the theory of 3 colors...

  • thank you for migration, I will not do that mistake anymore. – enguerran Dec 17 '09 at 9:51

Just go Format|Conditional Formatting and do Cell Value is equal to Hold and give that yellow formatting. Then do the same for Progress. Am I missing the point?

EDIT: The Four colour theorem doesn't work in this case. You could conceivably have hundreds of colours, so unless you pick each one (you're not using numeric values so you can't use numbers to decide) I don't think this would work.

  • It is a perfect answer BUT I have at least 200 distinct values... Too much for one finger on a little mouse! – enguerran Dec 16 '09 at 14:29
  • I don't think you can apply the 3 colours theory to this kind of data - it looks like you could end up with a lot of different colours... – Skilldrick Dec 16 '09 at 14:37
  • Could you use a VLOOKUP() function to map the words to color values? – Chris Nava Dec 17 '09 at 3:14
  • I meaned that 2 consecutive colors cannot be the same... It is not the 3 colours theory at all, apologize. ^^ – enguerran Dec 17 '09 at 17:01

You can write a formula which returns 0 or 1 depending on whether it finds the one criteria. Then create multiple conditional formatting for the same range, which uses the output of the formula. Alternatively make the formula more complex and have it return 0,1, .... n to represent a full state (such as has "Y", does not have "x", has "z"). Again, use overlapping conditional formating. Also use named ranges!


Your question is a bit ambiguous so I'll try for each option..

1) If you want to format a row based on a cell value...you're a bit out of luck. You can only format a cell based on its value, so if you select a row...it's the values in the row you're basing it on. The only way I'm aware of doing this would be some VBA coding - others may have a suggestion though.

2) If you want to format a cell differently based on the text in it...select the cell, go to format->conditional formatting. Choose the same cell in the conditional term. Leave the condition as "Cell Value is" and specify the format to use based on that text. You can then add more conditions for each potential text value.

  • My issue is the merge of both answer... 1) out of luck > Need to vba develop 2) Too many distinct values to accept this answer – enguerran Dec 16 '09 at 14:30
  • 1
    Then I'm afraid it's probably going to require some VBA scripting. It would only need a few lines really, a loop going through the cells changing when required, and a button to activate it? – Mark Mayo Dec 16 '09 at 14:35
  • Yes I am writing it ;) – enguerran Dec 16 '09 at 14:41
  • Using conditional Formatting, you certainly CAN format all the cells in a row based on the value of ONE cell in that row without any VBA programming. The example given shows only three colors being used (blue, yellow and no-color). Conditional Formatting in XL 2003 can handle three colors plus no-color, and there some wiggle room for additional formatting colors above that. I do not know if that has been expanded in XL-2007. – Kije Dec 17 '09 at 2:53
  • (Continued...) The limiting factor is the number colors you want, not the number of distinct values you wish to use to choose the colors. – Kije Dec 17 '09 at 2:56

I answer my own question to post some VBA code:

Sub COLOR(area As Range)
Dim c As Range
Dim a As Long
Dim v As Variant

Dim values As Variant
values = DistinctValues(area)
a = 0
For Each v In values
    a = a Mod 6 + 1
    FindAll(area, v, LookIn:=xlValues, LookAt:=xlWhole).EntireRow.SpecialCells(xlCellTypeConstants).Interior.ThemeColor = a + 4
End Sub

You can find an implementation of FindAll and DistinctValue.

I am aware the code is quick and dirty, as VBA is. :-p

Some explanation

  • Range.Interior.ThemeColor is the 2007 excel properties to fit color with some theme colors set.
  • a Mod 6 + 1 and a + 4 are use to choose between the 6 colours (Accent[1-6]) from the 12 of a theme.
  • Range.EntireRow.SpecialCells(xlCellTypeConstants) returns the whole with content cells of the row. area is the range where the duplicates are.

Use "Conditional Formating" and then "new Rule" to format:

enter image description here

  • You'll note from his screenshot that his version of Excel has a completely different menu system...in his version it's under Format->Conditional Formatting. – Mark Mayo Dec 16 '09 at 14:26
  • It is a screenshot fetched on the internet, I am under proxies and I can't publish my screenshot for SO. Excel version is 2003 or 2007 – enguerran Dec 16 '09 at 14:28
  • @Mark: he says Excel 2007 in the question and in the tags... – Eduardo Molteni Dec 16 '09 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.