conditional format macro formula
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 |
conditional format macro formula
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. -- Regards, Tom Ogilvy "Todd" wrote: 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 |
All times are GMT +1. The time now is 01:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com