Need to insert value based on Background Color - 8KB Attach
 

You need to check the interior.colorindex of the cells, but for the
"standard" colors:

for each cell in selection
Select Case cell.Interior.ColorIndex
Case 3 'Red
Cell.Value = "Severe"
Case 4 'Green
cell.Value = "Slight"
Case 6 'Yellow
Cell.Value = "Minor"
End Select
Next

--
Regards,
Tom Ogilvy

"RT" wrote in message
...
I have to get a spreadsheet setup so I can import into Access. Certain
parts of it are color coded, Green - slight, Yellow = Minor, Red=Severe.
I
need to insert in the cell it's value. See attached.

Sorry for the binary but it's small. It's just so much easier than trying
to explain.

Thanks

RRT

Need to insert value based on Background Color - 8KB Attach
 

You need to check the interior.colorindex of the cells, but for the
"standard" colors:

for each cell in selection
Select Case cell.Interior.ColorIndex
Case 3 'Red
Cell.Value = "Severe"
Case 4 'Green
cell.Value = "Slight"
Case 6 'Yellow
Cell.Value = "Minor"
End Select
Next

--
Regards,
Tom Ogilvy

In trying to get this working on just one cell I've tried:

Sub Macro2()
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

End Sub

What am i leaving out?

Need to insert value based on Background Color - 8KB Attach
 

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

Just select one or more cells.
less robust (only works on one cell)

Sub Macro2()
Select Case Activecell.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
End Sub

--
Regards,
Tom Ogilvy

"RT" wrote in message
...

You need to check the interior.colorindex of the cells, but for the
"standard" colors:

for each cell in selection
Select Case cell.Interior.ColorIndex
Case 3 'Red
Cell.Value = "Severe"
Case 4 'Green
cell.Value = "Slight"
Case 6 'Yellow
Cell.Value = "Minor"
End Select
Next

--
Regards,
Tom Ogilvy

In trying to get this working on just one cell I've tried:

Sub Macro2()
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

End Sub

What am i leaving out?

Need to insert value based on Background Color - 8KB Attach
 

The colors in the Options dialog are not in any particular order.
To determine a cells color index, color the cell as desired, then
run the following code:

Sub ShowColorIndex()
MsgBox "Color Index: " & ActiveCell.Interior.ColorIndex
End Sub

To create a list of all color indexes, run the following code in
a blank worksheet:

Sub CreateColors()
Dim N As Integer
For N = 1 To 56
Cells(N,1).Value = N
Cells(N,2).Interior.ColorIndex = N
Next N
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"RT" wrote in message
...
I must be using the wrong number for colors. When I run the
macro nothing happens.
For the reddish color I assume 46 (see attached). Are they
numbered left to right 1, 2, 3 ?

RRT

"Tom Ogilvy" wrote in message
...
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

Just select one or more cells.
less robust (only works on one cell)

Sub Macro2()
Select Case Activecell.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
End Sub

--
Regards,
Tom Ogilvy

"RT" wrote in message
...

You need to check the interior.colorindex of the cells, but
for the
"standard" colors:

for each cell in selection
Select Case cell.Interior.ColorIndex
Case 3 'Red
Cell.Value = "Severe"
Case 4 'Green
cell.Value = "Slight"
Case 6 'Yellow
Cell.Value = "Minor"
End Select
Next

--
Regards,
Tom Ogilvy

In trying to get this working on just one cell I've tried:

Sub Macro2()
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

End Sub

What am i leaving out?

Need to insert value based on Background Color - 8KB Attach
 

Many thanks, I finally got this one to work. I did have to do this
although:

Case 46 'Color from 6th row 6th over
cell.Value = "Severe"

change cell.value to Activecell.Value.


"Chip Pearson" wrote in message
...
The colors in the Options dialog are not in any particular order. To
determine a cells color index, color the cell as desired, then run the
following code:

Sub ShowColorIndex()
MsgBox "Color Index: " & ActiveCell.Interior.ColorIndex
End Sub

To create a list of all color indexes, run the following code in a blank
worksheet:

Sub CreateColors()
Dim N As Integer
For N = 1 To 56
Cells(N,1).Value = N
Cells(N,2).Interior.ColorIndex = N
Next N
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"RT" wrote in message
...
I must be using the wrong number for colors. When I run the macro nothing
happens.
For the reddish color I assume 46 (see attached). Are they numbered left
to right 1, 2, 3 ?

RRT

"Tom Ogilvy" wrote in message
...
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

Just select one or more cells.
less robust (only works on one cell)

Sub Macro2()
Select Case Activecell.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
End Sub

--
Regards,
Tom Ogilvy

"RT" wrote in message
...

You need to check the interior.colorindex of the cells, but for the
"standard" colors:

for each cell in selection
Select Case cell.Interior.ColorIndex
Case 3 'Red
Cell.Value = "Severe"
Case 4 'Green
cell.Value = "Slight"
Case 6 'Yellow
Cell.Value = "Minor"
End Select
Next

--
Regards,
Tom Ogilvy

In trying to get this working on just one cell I've tried:

Sub Macro2()
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

End Sub

What am i leaving out?

Need to insert value based on Background Color - 8KB Attach
 

Actually, if you used the first suggestion, you wouldn't have to, but you
are right. For the work with only one cell approach, you have to change
cell to ActiveCell under each case statement. My fault for failing to
change this. Apologies.

--
Regards,
Tom Ogilvy

"RT" wrote in message
...
Many thanks, I finally got this one to work. I did have to do this
although:

Case 46 'Color from 6th row 6th over
cell.Value = "Severe"

change cell.value to Activecell.Value.


"Chip Pearson" wrote in message
...
The colors in the Options dialog are not in any particular order. To
determine a cells color index, color the cell as desired, then run the
following code:

Sub ShowColorIndex()
MsgBox "Color Index: " & ActiveCell.Interior.ColorIndex
End Sub

To create a list of all color indexes, run the following code in a blank
worksheet:

Sub CreateColors()
Dim N As Integer
For N = 1 To 56
Cells(N,1).Value = N
Cells(N,2).Interior.ColorIndex = N
Next N
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"RT" wrote in message
...
I must be using the wrong number for colors. When I run the macro
nothing
happens.
For the reddish color I assume 46 (see attached). Are they numbered
left
to right 1, 2, 3 ?

RRT

"Tom Ogilvy" wrote in message
...
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

Just select one or more cells.
less robust (only works on one cell)

Sub Macro2()
Select Case Activecell.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
End Sub

--
Regards,
Tom Ogilvy

"RT" wrote in message
...

You need to check the interior.colorindex of the cells, but for the
"standard" colors:

for each cell in selection
Select Case cell.Interior.ColorIndex
Case 3 'Red
Cell.Value = "Severe"
Case 4 'Green
cell.Value = "Slight"
Case 6 'Yellow
Cell.Value = "Minor"
End Select
Next

--
Regards,
Tom Ogilvy

In trying to get this working on just one cell I've tried:

Sub Macro2()
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

End Sub

What am i leaving out?

Need to insert value based on Background Color - 8KB Attach
 

You can't assume.

select a cell, then run a macro like this

Sub showcolorIndex
msgbox activeCell.Interior.ColorIndex
End Sub

No, they are not numbered in order as they appear on the palatte.

--
Regards,
Tom Ogilvy

"RT" wrote in message
...
I must be using the wrong number for colors. When I run the macro nothing
happens.
For the reddish color I assume 46 (see attached). Are they numbered left
to
right 1, 2, 3 ?

RRT

"Tom Ogilvy" wrote in message
...
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

Just select one or more cells.
less robust (only works on one cell)

Sub Macro2()
Select Case Activecell.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
End Sub

--
Regards,
Tom Ogilvy

"RT" wrote in message
...

You need to check the interior.colorindex of the cells, but for the
"standard" colors:

for each cell in selection
Select Case cell.Interior.ColorIndex
Case 3 'Red
Cell.Value = "Severe"
Case 4 'Green
cell.Value = "Slight"
Case 6 'Yellow
Cell.Value = "Minor"
End Select
Next

--
Regards,
Tom Ogilvy

In trying to get this working on just one cell I've tried:

Sub Macro2()
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

End Sub

What am i leaving out?