Excell 2000 Newbie question - Formatting

  • Thread starter Thread starter Matthew Salerno
  • Start date Start date
M

Matthew Salerno

Excel 2000
Windows 2000

I need some assistance, guidance or inspiration on how to complete a certain
task within excel. Here is my scenario, Column "C" throughout our
spreadsheet is a status indicator, we have specific codes that get entered
into this cell. I would like to setup the spreadsheet so that if a certain
status is entered in Column "C" the entire row will be highlighted a certain
color based on the status. I know how to do this with conditional
formatting, but the problem is that there are more than 3 different
conditions.

Example,
Col C = bk - Row highlights yellow
Col C = bn - Row highlights blue
etc...

There are many more, but you all get the idea. I have been looking all
around the internet for a solution, but I have been unable to get any of the
examples to work for me.

Any assistance will be apprecited.

Regards,

Matt
 
Excel 2000
Windows 2000

I need some assistance, guidance or inspiration on how to complete a certain
task within excel. Here is my scenario, Column "C" throughout our
spreadsheet is a status indicator, we have specific codes that get entered
into this cell. I would like to setup the spreadsheet so that if a certain
status is entered in Column "C" the entire row will be highlighted a certain
color based on the status. I know how to do this with conditional
formatting, but the problem is that there are more than 3 different
conditions.

Example,
Col C = bk - Row highlights yellow
Col C = bn - Row highlights blue
etc...

There are many more, but you all get the idea. I have been looking all
around the internet for a solution, but I have been unable to get any of the
examples to work for me.

Any assistance will be apprecited.

Regards,

Matt
Click to expand...

Sorry, I forgot to include the fact that I protect the workbook before it is
distributed.

Thanks.
 
Matthew

Copy/paste this event code to the worksheet in question.

Right-click on the tab and "View Code". Paste in there.

Adjust "Cases" to suit.



Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("C:C"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP
 
Gord Dibben said:
Matthew

Copy/paste this event code to the worksheet in question.

Right-click on the tab and "View Code". Paste in there.

Adjust "Cases" to suit.



Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("C:C"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Gord Dibben Excel MVP
Click to expand...

WOW ! That is exactly what I was looking for. The only change I needed to
make was to add:

ActiveSheet.Unprotect "password"

at the top
and

ActiveSheet.Protect "password"

at the bottom

Thanks again
 
Matthew

Glad to have been of assistance.

Gord

WOW ! That is exactly what I was looking for. The only change I needed to
make was to add:

ActiveSheet.Unprotect "password"

at the top
and

ActiveSheet.Protect "password"

at the bottom

Thanks again
Click to expand...
 
Back
Top