conditional format macro formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Todd - 20 Mar 2006 18:02 GMT

Hi I am learning to use macro's and am having trouble determining the formula
to put in a macro. ( I only have two variables and I know that conditional
formatting can do this but I am trying to learn what to do.) My variables
are that the cell would contain either more than 4 digits or that it would
contain the word "workbook".

How do I write the formula to put into this formatting macro?

Thanks.

Sub ColorRowBasedOnCellValue()
'David McRitchie, 2001-01-17 programming -- Color row based on value
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 >= "workbook"
cell.EntireRow.Interior.ColorIndex = 20
Case Is >= 40
cell.EntireRow.Interior.ColorIndex = 37
End Select
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

Reply to this Message

Tom Ogilvy - 20 Mar 2006 18:30 GMT

If it will only contain 2 things then

Sub ColorRowBasedOnCellValue()
'David McRitchie, 2001-01-17 programming -- Color row based on value
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
For Each cell In Intersect(Selection, ActiveCell.EntireColumn, _
ActiveSheet.UsedRange)
if len(trim(cell.Value)) = 0 then
' nothing
cell.Interior.ColorIndex = xlNone
elseif len(cell.Value) = 4 then
cell.Interior.ColorIndex = 37
elseif instr(1,cell.Value,"workbook",vbtextcompare) then
cell.interior.colorIndex = 20
else
' shouldn't get here but if you do make it red
cell.Interior.ColorIndex = 3
end if
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

Modify to suit.

Signature

Regards,
Tom Ogilvy

> Hi I am learning to use macro's and am having trouble determining the formula
> to put in a macro. ( I only have two variables and I know that conditional
[quoted text clipped - 23 lines]
> Application.ScreenUpdating = False
> End Sub

Reply to this Message