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 (3)
Please login or register to join the discussion.
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
Here's a link to my attempt in 2003
Though this was trying to achieve something slightly different to what you want