Excel Visual Basic Code?

Question

Can anyone provide me with code that changes a cell's background color based on the value of the cell above it? For instance if cell A1 has a value of 0-7 then A2 will be shaded green, if A1 has a value of 8-12 then A2 will be shaded yellow etc.... Thanks in advance!

I know that conditional formatting exists, but i need to do this for more than 3 possiblities (5 or so)

Answer 1

Paste this function into your module

Public sub ShadeMe(CellAddress)
select case range(celladdress).value
case 0 to 7
range(celladdress).offset(1). Interior.ColorIndex = rgb(255,255,0)
case 8 to 15
range(celladdress).offset(1). Interior.ColorIndex = rgb(255,0,0)
case else
range(celladdress).offset(1). Interior.ColorIndex = rgb(0,0,0)
end select
end sub

Now Use this line to call it
shademe "A1"
this will shade cell A2 based on value in A1
the colors and conditions can be added easily.
Add new line of "case ..." for more condition
Add RGB(Red color, Green, Blue) to get the color you want in that condition

Contact me for more info

Read my profile, I am XLMan

Answer 2

I think Excel should provide more than three conditions in the conditional formatting, but anyway. Here's how I would do this:

Right click on the tab name of your sheet and choose "View Code." Under General change it to worksheet. It will put in a sub header for SelectionChange, just delete that code.

Paste in the code below. Because the sub is called Worksheet_Change(), the code will run everytime the sheet is changed. It may get slow if you have a lot of cells to shade.

Code starts immediately below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And InStr(Target.Address, ":") = 0 Then
'only process when entries are made in row 1 and a multi-cell range is not selected.
Select Case Range(Target.Address) 'Cell evaluated
Case 0 To 7
Range(Target.Address).Offset(1, 0).Interior.ColorIndex = 50 'Green
Case 8 To 12
Range(Target.Address).Offset(1, 0).Interior.ColorIndex = 6 'Yellow
Case 13 To 17
Range(Target.Address).Offset(1, 0).Interior.ColorIndex = 9 'Red
Case 18 To 22
Range(Target.Address).Offset(1, 0).Interior.ColorIndex = 11 'Blue
Case 23 To 27
Range(Target.Address).Offset(1, 0).Interior.ColorIndex = 46 'Orange
Case Else
Range(Target.Address).Offset(1, 0).Interior.ColorIndex = 0 ' None
End Select
End If
End Sub


END OF CODE ABOVE AT END SUB

I like XLman's answer, but I thought it would be better to have the colors shaded automatically instead of having to run the code some way. This was a fun project, thanks for asking this question!

Answer 3

Sub soltep()
Column_To_Check = 1
Column_To_Color = 2
For Rows_Counter = 1 To Cells(1, 1). CurrentRegion. Rows. Count
Select Case Cells( Rows_Counter, Column_To_Check)
Case 0 To 7
Background_Color = 3
Case 8 To 12
Background_Color = 4
Case 13 To 19
Background_Color = 5
Case 20 To 27
Background_Color = 6
Case Else
Background_Color = 7
End Select
Cells( Rows_Counter, Column_To_Color). Interior.ColorIndex = Background_Color
' // The following line will color the entire row - not just the cell
'Rows( Rows_Counter). Interior. ColorIndex = Background_Color
Next Rows_Counter
End Sub

Answer 4

You don't need to create VB code or even run a Macro. The functionality that you desire is already built into Excel. Just click on Format --> Conditional Formatting and select the cells and formatting (color) by clicking the format button. Also, use the drop-down lists to select the values you want. Hopefully, that helps!