Share this content
0
3
1958

Conditional formatting help in excel needed please

Hi, I have a column in my spreadsheet for recording the progress of a task for the client in that row. In that column I'll enter either c (as in "c for charlie"), 1, 2, 3, 4 or 5. I would like the whole row to turn a certain colour (different for each) once the progress number (or letter c) is entered. I know how to use conditional formatting to make the whole row turn a certain cololur when one of those characters are entered, but as there are 6 I would need 6 conditions/crtieria boxes on the conditional formatting menu but there are only 3. Hope that makes sense!

Does anyone know a way round this? I don't have excel 2007 and there are no plans to upgrade any time soon in our office. I've searched the web and one website suggests using the OR function but I don't think this would work as I have 6 different formats. Another website lists some VB code which I tried out but it was just to make a single cell that colour. I am not very experienced with this so if someone would be kind enough to give me a noddy copy and paste job if VB is the only way then I would be very grateful! I have the index numbers ready of the colours I want for each progress number/letter.

Replies

Please login or register to join the discussion.

avatar
By ACDWebb
24th Nov 2009 18:10

Try using this VB code

You will need to put it in the section called Excel Objects and the sheet that you want to be affected, NOT a standard VB code module

Experiment with the colour codes

Code assumes you enter the letter/number in column A (ie 1) Change the column number as necessary

'Code starts

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim rColour 'to hold colour code
 
 If Target.Column = 1 Then 'is the entry in the required column
  Select Case Target 'work out what has been entered
   Case "c"
    rColour = 26
   Case 1
    rColour = 27
   Case 2
    rColour = 28
   Case 3
    rColour = 3
   Case 4
    rColour = 4
   Case 5
    rColour = 22
   Case Else 'if none of the above jump to NoFill and remove row colouring
    GoTo NoFill
  End Select
  With Target.EntireRow.Interior 'set the colour of the row using colour code set above
      .ColorIndex = rColour
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
  End With
  GoTo Finish 'jump to the end
NoFill:
 Target.EntireRow.Interior.ColorIndex = xlNone
 End If
Finish:
 End Sub

'code ends

 And finally - as I expect someone will mention it and it probably has something that will do this - you could look at ASAP Utilities

Thanks (0)
avatar
By ACDWebb
25th Nov 2009 06:38

Here's a link to my attempt in 2003

LINK

Though this was trying to achieve something slightly different to what you want

Thanks (0)
avatar
By Anonymous
25th Nov 2009 11:24

Thanks Alan that's brilliant! :-D

Thanks (0)