2

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

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

5 Answers 5

1

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.

4
  • 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, 2009 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, 2009 at 14:37
  • Could you use a VLOOKUP() function to map the words to color values?
    – Chris Nava
    Dec 17, 2009 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, 2009 at 17:01
1

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!

0

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.

5
  • 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, 2009 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, 2009 at 14:35
  • Yes I am writing it ;)
    – enguerran
    Dec 16, 2009 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, 2009 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, 2009 at 2:56
0

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
Next
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.
0

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

enter image description here

3
  • 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, 2009 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, 2009 at 14:28
  • @Mark: he says Excel 2007 in the question and in the tags... Dec 16, 2009 at 15:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .