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

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

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

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

On Mar 20, 7:18 am, "Ray" wrote:
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

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

On Mar 20, 8:24 am, "Susan" wrote:
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

On Mar 20, 7:18 am, "Ray" wrote:

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

Thanks Susan ... worked perfectly!

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

yipee!! i got one right!
:)
susan

On Mar 20, 9:20 am, "Ray" wrote:
On Mar 20, 8:24 am, "Susan" wrote:





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

On Mar 20, 7:18 am, "Ray" wrote:

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

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

- Show quoted text -