Changing cell color based on its value
 

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

--
Message posted from http://www.ExcelForum.com

Changing cell color based on its value
 

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?


--
Regards
Frank Kabel
Frankfurt, Germany


Hi I'am trying to create a macro which should change the backround
color of a cell based on its value in column B. My code is going
through, but it does't do any coloring, whats wrong, am I somehow
wrong referring to 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 Sub


---
Message posted from http://www.ExcelForum.com/

Changing cell color based on its value
 

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

--
Regards,
Tom Ogilvy


"spolk " wrote in message
...
Hi I'am trying to create a macro which should change the backround color
of a cell based on its value in column B. My code is going through, but
it does't do any coloring, whats wrong, am I somehow wrong referring to
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 Sub


---
Message posted from http://www.ExcelForum.com/

Changing cell color based on its value
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Frank Kabel wrote:
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?



Hi I'am trying to create a macro which should change the backround
color of a cell based on its value in column B. My code is going
through, but it does't do any coloring, whats wrong, am I somehow
wrong referring to 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 Sub


---
Message posted from http://www.ExcelForum.com/

Changing cell color based on its value
 

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

--
Don Guillett
SalesAid Software

"spolk " wrote in message
...
Hi I'am trying to create a macro which should change the backround color
of a cell based on its value in column B. My code is going through, but
it does't do any coloring, whats wrong, am I somehow wrong referring to
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 Sub


---
Message posted from http://www.ExcelForum.com/

Changing cell color based on its value
 

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Frank Kabel" wrote in message ...
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?


--
Regards
Frank Kabel
Frankfurt, Germany


Hi I'am trying to create a macro which should change the backround
color of a cell based on its value in column B. My code is going
through, but it does't do any coloring, whats wrong, am I somehow
wrong referring to 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 Sub


---
Message posted from http://www.ExcelForum.com/

Changing cell color based on its value
 

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Changing cell color based on its value
 

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

"Don Guillett" wrote in message
...
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

--
Don Guillett
SalesAid Software

"spolk " wrote in message
...
Hi I'am trying to create a macro which should change the backround color
of a cell based on its value in column B. My code is going through, but
it does't do any coloring, whats wrong, am I somehow wrong referring to
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 Sub


---
Message posted from http://www.ExcelForum.com/

Changing cell color based on its value
 

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))
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"spolk " wrote in message
...
Hi I'am trying to create a macro which should change the backround color
of a cell based on its value in column B. My code is going through, but
it does't do any coloring, whats wrong, am I somehow wrong referring to
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 Sub


---
Message posted from http://www.ExcelForum.com/

Changing cell color based on its value
 

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.


---
Message posted from http://www.ExcelForum.com/