Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - If Else problem - HELP PLEASE
I am trying to write some code where I start the cursor in a workbook,
and it loops down until the cell in that column is empty changing the colours of the cells on the way depending what is in the cell. Here is my attempted effort. Any help would be great, Thanks Do If IsEmpty(ActiveCell) = False Then ElseIf ActiveCell = "zone total" Then ActiveCell.Select With Selection.Interior ColorIndex = 12 Pattern = xlSolid ActiveCell.Offset(1, 0).Select ElseIf ActiveCell = "regional total" Then ActiveCell.Select With Selection.Interior ColorIndex = 45 Pattern = xlSolid ActiveCell.Offset(1, 0).Select Else ActiveCell.Select With Selection.Interior ColorIndex = 3 Pattern = xlSolid ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True End Sub:) --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - If Else problem - HELP PLEASE
A couple of options:
Sub ChangeColours() Dim lCurrentColumn As Long ' store for current column number Dim lCurrentRow As Long ' store for current row number Dim lLastRow As Long ' store for last row number Dim lCount As Long ' row counter lCurrentColumn = ActiveCell.Column ' save current column lCurrentRow = ActiveCell.Row ' save current row lLastRow = Cells(Rows.Count, lCurrentColumn).End(xlUp).Row 'MsgBox "CC " & lCurrentColumn & _ " CR " & lCurrentRow & _ " LR " & lLastRow Application.ScreenUpdating = False For lCount = lCurrentRow To lLastRow If IsEmpty(Cells(lCount, lCurrentColumn)) _ = True Then ' do nothing ElseIf LCase(Cells(lCount, lCurrentColumn)) _ = "zone total" Then With Cells(lCount, lCurrentColumn).Interior .ColorIndex = 12 .Pattern = xlSolid End With ElseIf LCase(Cells(lCount, lCurrentColumn)) _ = "regional total" Then With Cells(lCount, lCurrentColumn).Interior .ColorIndex = 45 .Pattern = xlSolid End With Else With Cells(lCount, lCurrentColumn).Interior .ColorIndex = 3 .Pattern = xlSolid End With End If Next 'lCount Application.ScreenUpdating = True End Sub ' Or with the Select Case approach ... Sub ChangeColoursSC() Dim lCurrentColumn As Long ' store for current column number Dim lCurrentRow As Long ' store for current row number Dim lLastRow As Long ' store for last row number Dim lCount As Long ' row counter lCurrentColumn = ActiveCell.Column ' save current column lCurrentRow = ActiveCell.Row ' save current row lLastRow = Cells(Rows.Count, lCurrentColumn).End(xlUp).Row 'MsgBox "CC " & lCurrentColumn & _ " CR " & lCurrentRow & _ " LR " & lLastRow Application.ScreenUpdating = False For lCount = lCurrentRow To lLastRow Select Case Cells(lCount, lCurrentColumn) Case Empty ' Do nothing Case "zone total" With Cells(lCount, lCurrentColumn).Interior .ColorIndex = 12 .Pattern = xlSolid End With Case "regional total" With Cells(lCount, lCurrentColumn).Interior .ColorIndex = 45 .Pattern = xlSolid End With Case Else With Cells(lCount, lCurrentColumn).Interior .ColorIndex = 3 .Pattern = xlSolid End With End Select Next 'lCount Application.ScreenUpdating = True End Sub They don't select any cells so they should be quicker, particularly if there are a lot of rows. Regards Trevor "Xispo " wrote in message ... I am trying to write some code where I start the cursor in a workbook, and it loops down until the cell in that column is empty changing the colours of the cells on the way depending what is in the cell. Here is my attempted effort. Any help would be great, Thanks Do If IsEmpty(ActiveCell) = False Then ElseIf ActiveCell = "zone total" Then ActiveCell.Select With Selection.Interior ColorIndex = 12 Pattern = xlSolid ActiveCell.Offset(1, 0).Select ElseIf ActiveCell = "regional total" Then ActiveCell.Select With Selection.Interior ColorIndex = 45 Pattern = xlSolid ActiveCell.Offset(1, 0).Select Else ActiveCell.Select With Selection.Interior ColorIndex = 3 Pattern = xlSolid ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True End Sub:) --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Problem viewing Excel 2003 Pivot Chart fields in Excel 2007 | Charts and Charting in Excel | |||
Weird problem with Excel 2000...Worksheets disappearing in a shared Excel file | Excel Discussion (Misc queries) | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Excel 97 chart opened in Excel 2003 - Source Data problem | Charts and Charting in Excel |