Changing cell color based on its value

[spolk]

Hi I'am trying to create a macro which should change the backround colo
of a cell based on its value in column B. My code is going through, bu
it does't do any coloring, whats wrong, am I somehow wrong referring t
to the column B?


Sub ColorCellBasedOnCellValue()
'Colors a cell in column based on its value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Cells(Rows.Count, "B")
Select Case cell.Value
Case Is = 20
cell.Interior.ColorIndex = 7
Case Is = 0
cell.Interior.ColorIndex = 7


End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Su

 

[Frank Kabel]

Hi
try
Sub ColorCellBasedOnCellValue()
'Colors a cell in column based on its value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Cells(Rows.Count, "B")
Select Case cell.Value
Case 20
cell.Interior.ColorIndex = 7
Case 0
cell.Interior.ColorIndex = 7
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

But why don't you use 'Format - Conditional Format' for this?

 

[Tom Ogilvy]

Sub ColorCellBasedOnCellValue()
'Colors a cell in column based on its value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Range("B1",Cells(Rows.Count, "B").end(xlup))
Select Case cell.Value
Case Is = 20
cell.Interior.ColorIndex = 7
Case Is = 0
cell.Interior.ColorIndex = 7
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

Colors the cells in column B if they are 20 or 0

 

[Frank Kabel]

Hi
forgot changing the For clause. Try changing
For Each cell In Cells(Rows.Count, "B")
to
For Each cell In Range("B:B")

 

[Don Guillett]

try this instead. You did not have a range.
Sub ColorCellBasedOnCellValue()
'Colors a cell in column based on its value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range

'For Each cell In Cells(Rows.Count, "B")
For Each cell In Range("b1:b" & Cells(Rows.Count, "B").End(xlUp).Row)

Select Case cell.Value
Case 20
cell.Interior.ColorIndex = 7
Case 0
cell.Interior.ColorIndex = 0
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

 

[David McRitchie]

Isn't that a single cell at the bottom of the worksheet.

perhaps:

For Each cell In Column("B")
but that would be rather bad coding, how about

For Each cell in intersect(Column("B"), UsedRange)

For more information see
http://www.mvps.org/dmcritchie/excel/proper.htm
if you can limit it to text only cells, or formula only cells.
i.e.

For Each cell in intersect(Column("B"), _
Selection.SpecialCells(xlNumbers))


Also see Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
Formula 1 is: =OR(A1=0,B1=20)
if A1 is the active cell when you assign C.F.

 

[David McRitchie]

Hi ....,
Oops don't need intersect:

Sub ColorCellBasedOnCellValue()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Range("B:B").SpecialCells(xlConstants, xlNumbers)
If cell.Value = 0 Or cell.Value = 20 Then
cell.Interior.ColorIndex = 7
End If
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

If in fact you wanted to have 7 for both 20 & 0 and you really need select
then
Case 20, 0:cell.Interior.ColorIndex = 7
Sub ColorCellBasedOnCellValue()
'Colors a cell in column based on its value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Range("b1:b" & Cells(Rows.Count, "B").End(xlUp).Row)
Select Case cell.Value
Case 20, 0: cell.Interior.ColorIndex = 7
Case 10: cell.Interior.ColorIndex = 6
Case Else: cell.Interior.ColorIndex = 0
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

try this instead. You did not have a range.
Sub ColorCellBasedOnCellValue()
'Colors a cell in column based on its value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range

'For Each cell In Cells(Rows.Count, "B")
For Each cell In Range("b1:b" & Cells(Rows.Count, "B").End(xlUp).Row)

Select Case cell.Value
Case 20
cell.Interior.ColorIndex = 7
Case 0
cell.Interior.ColorIndex = 0
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

 

[Earl Kiosterud]

spolk,

Cells(Rows.Count, "B") is equivalent to Cells(65,536, "B"). You code will
operate on only the bottommost cell in column B. Try something like:
For Each cell In Intersect(ActiveSheet.UsedRange, Cells(1,
"B").EntireColumn)
or:
For Each cell In Range(Cells(1, "B"), Cells(Cells.Rows.Count,
"B").End(xlUp))

 

[spolk]

OK , now i understood how i should code this, thanks you all guys. I
don't want to use conditional formatting in this, because i want this
system to be expandable including more than three conditions. And I
can't gewt condional formatting to work absolutely as I want to.
Pasting seems to be problem in conditional formatting, but this macro
system should be "bomb-sure" concerning pasting values.