Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Sum Based on Cell Background Color | Excel Worksheet Functions | |||
change background color based on range value | New Users to Excel | |||
Change background color based on value in column A | Excel Discussion (Misc queries) | |||
Changing background color based on different cell | Excel Discussion (Misc queries) | |||
Setting cell background color based on value | Excel Programming |