Insert Value based on ColorIndex, for specific range on all sheets

[Ray]

Hello -

Searching through some old posts, I found this code to use
Interior.ColorIndex to assign a value to a cell. I need to modify
this code to apply this logic to all sheets in a workbook, for a
specific range (say A1-Z100) .... OR even better would be to apply to
the 'used' range on each sheet (since it will vary).

TIA, Ray


Sub Macro2()
Dim cell as Range
for each cell in selection
Select Case cell.Interior.ColorIndex
Case 46 'Color from 6th row 6th over
cell.Value = "Severe"
Case 20 'Green
cell.Value = "Slight"
Case 26 'Yellow
cell.Value = "Minor"
End Select
Next Cell
End Sub

 

[Susan]

additions don't have >

Sub Macro2()
Dim cell as Range

MsgBox ActiveSheet.UsedRange.Address
'you could also try
'MsgBox ActiveSheet.CurrentRegion.Address
'this is just for testing purposes
'to make sure you have the right range.
'& can be deleted once macro
'is working properly
ActiveSheet.UsedRange.Select

for each cell in selection
Select Case cell.Interior.ColorIndex
Case 46 'Color from 6th row 6th over
cell.Value = "Severe"
Case 20 'Green
cell.Value = "Slight"
Case 26 'Yellow
cell.Value = "Minor"
End Select
Next Cell
End Sub

i didn't test this but it should work.

susan

 

[Ray]

additions don't have >


MsgBox ActiveSheet.UsedRange.Address
'you could also try
'MsgBox ActiveSheet.CurrentRegion.Address
'this is just for testing purposes
'to make sure you have the right range.
'& can be deleted once macro
'is working properly
ActiveSheet.UsedRange.Select


i didn't test this but it should work.

susan

Thanks Susan ... worked perfectly!

 

[Susan]

yipee!! i got one right!

susan

Thanks Susan ... worked perfectly!- Hide quoted text -

- Show quoted text -