Loop Code?

[documike]

I have a macro that when run changes the color of a row based on a cell
value.
Col A has TRUE & FALSE values and the color of the row changes whether TRUE
or FALSE.
It works fine when I manually run the macro.
I want it to dynamically change whenever one of the cells in Col A changes.
Is this when a LOOP is required? How do I code that? Here is the code I am
currently playing with:

Sub ColorRowBasedOnCellValue()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Intersect(Selection, ActiveCell.EntireColumn, _
ActiveSheet.UsedRange)
Select Case cell.Value
Case Is >= TRUE
cell.EntireRow.Interior.colorindex = 20
Case Is >= FALSE
cell.EntireRow.Interior.colorindex = 37
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

 

[Dave Peterson]

Instead of using code, you may want to look at:
Format|conditional formatting

But if you have to use code, how are the cells changing.

If it's by typing, you could use a worksheet_change event and just check the
cells that change.

 

[Harald Staff]

Hi

I think you are better off using "conditional formatting" for this. See
http://www.contextures.com/xlCondFormat01.html
Follow the procedure at http://j-walk.com/ss/excel/usertips/tip043.htm and
use condition Formula is: $A1=TRUE

If you want to proceed your way; call your existing macro from the
worksheet_change event. See
http://www.cpearson.com/excel/events.htm
on events.

HTH. Best wishes Harald

 

[Earl Kiosterud]

Mike,

I'm not sure why your using >= TRUE. Normally, you'd use = TRUE or = FALSE.

Your routine does loop through the cells, via the For Each cell in .... To
get it to change automatically, use an event procedure. Looping isn't
required:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then ' are we in column
A?
Select Case Target.Value
Case Is = True
Target.EntireRow.Interior.ColorIndex = 20
Case Is = False
Target.EntireRow.Interior.ColorIndex = 37
End Select

End If
End Sub

Put this in the sheet module, not a regular module.

 

[Dana DeLouis]

Just a general idea. If the cells only have True/False, perhaps another
option...

For Each cell In [Your Range Here].Cells
cell.EntireRow.Interior.ColorIndex = 37 - 17 * cell.Value
Next

Another option might be to use SpecialCells

Columns("A:A").SpecialCells(xlCellTypeConstants, xlLogical)...etc

 

[Harald Staff]

Hi Dana

Nice idea, but note that TRUE is -1 in VB / VBA.

Best wishes Harald

Just a general idea. If the cells only have True/False, perhaps another
option...

For Each cell In [Your Range Here].Cells
cell.EntireRow.Interior.ColorIndex = 37 - 17 * cell.Value
Next

Another option might be to use SpecialCells

Columns("A:A").SpecialCells(xlCellTypeConstants, xlLogical)...etc

--
Dana DeLouis
Win XP & Office 2003

I have a macro that when run changes the color of a row based on a cell
value.
Col A has TRUE & FALSE values and the color of the row changes whether
TRUE or FALSE.
It works fine when I manually run the macro.
I want it to dynamically change whenever one of the cells in Col A
changes. Is this when a LOOP is required? How do I code that? Here is
the code I am currently playing with:

Sub ColorRowBasedOnCellValue()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Intersect(Selection, ActiveCell.EntireColumn, _
ActiveSheet.UsedRange)
Select Case cell.Value
Case Is >= TRUE
cell.EntireRow.Interior.colorindex = 20
Case Is >= FALSE
cell.EntireRow.Interior.colorindex = 37
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub