Solved

Excel Makro : apply to all rows in cell   Columns("A:A").Select, its offset is wrong by 1

Posted on 2012-03-17
2
520 Views
Last Modified: 2012-03-18
Hi, I have the following 2 columns in an excel sheet

Date	                Time
12/12/2011	9:06:45
12/12/2011	9:06:45
12/11/2011	10:49:10
12/13/2011	10:54:21
12/14/2011	23:46:10
12/15/2011	12:10:55
12/16/2011	12:13:38
12/17/2011	12:22:13
12/18/2011	13:07:47
12/1/2011	19:25:50

Open in new window


code is simple, highlight if Column A has a weekend date in it

    Cells.FormatConditions.Delete
    
    Columns("A:A").Select
 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=WEEKDAY(A2,2)>5"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    

Open in new window


now the problem, it currently selects 1 row above the correct row
e.g.
12/12/2011       
12/12/2011       Highlighted, but it should highlight next row as the 12th is a monday
12/11/2011       
12/13/2011       
12/14/2011       
12/15/2011       
12/16/2011         Highlighted, , but it should highlight next row
12/17/2011        Highlighted correctly
12/18/2011        Not highlighted, but it should be
12/1/2011       

also, how can i stop highlighting any rows that have no values please? you will
see that the cells with no data (at the bottom) are all selected

thanks
Test-Date-Selection.xlsx
0
Comment
Question by:jxharding
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 400 total points
ID: 37733185
Try this.
Dim rng As Range

    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))

    With rng
        .FormatConditions.Delete


        .FormatConditions.Add Type:=xlExpression, Formula1:= _
                              "=WEEKDAY(A2,2)>5"
        .FormatConditions(1).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0
        End With

        .FormatConditions(1).StopIfTrue = False

    End With

Open in new window

0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 37733926
You might also consider this way, using R1C1 referencing instead of A1 referencing:

Sub Patrick()
    
    Dim rng As Range
    
    Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    With rng
        .FormatConditions.Delete
        .FormatConditions.Add xlExpression, , "=WEEKDAY(RC,2)>5"
        With .FormatConditions(1)
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0
            End With
            .StopIfTrue = True
        End With
    End With
    
End Sub

Open in new window



It takes a little getting used to for people accustomed to A1, but it handles the offsets in a much more intuitive fashion :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question