Share via

How can I correct this ActiveCell.Offset issue?

Dominick Branch 1 Reputation point
2021-02-05T14:38:19.173+00:00

Hello gang, I'm new with Macros and I'm looking for a solution to correct this ActiveCell.Offset issue within my code. I've put in bold the debugger issue in question. Please see below.

I've got hundreds of rows where i need to apply the same conditional format highlight on three separate occasions. I am staying within the same columns and have applied the reference source so I can't see my error.

Any help is appreciated!

Sub HighlightInRow()

'
' HighlightInRow Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
ActiveCell.Offset(7, 0).Range("A1,D1,G1,J1,M1,P1").Select
ActiveCell.Offset(7, 15).Range("A1").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=U8"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(0, -14).Range("A1,D1,G1,J1,M1,P1").Select
ActiveCell.Offset(0, 1).Range("A1").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=V8"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveCell.Offset(0, -14).Range("A1,D1,G1,J1,M1,P1").Select
ActiveCell.Offset(0, 1).Range("A1").Activate
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=W8"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nothing Left To Lose 396 Reputation points
    2021-02-06T16:04:08.557+00:00

    Re: code problem

    It is usually helpful to list the error number or description.
    In your case, it appears you are telling Excel to go off sheet...

    Your Offset commands are...
    ActiveCell.Offset(7, 15).Range("A1").Activate
    ActiveCell.Offset(0, -14).Range("A1,D1,G1,J1,M1,P1").Select
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ** ActiveCell.Offset(0, -14).Range("A1,D1,G1,J1,M1,P1").Select**
    ActiveCell.Offset(0, 1).Range("A1").Activate

    You go to the right by 16 cells (15 & 1) from the starting cell.
    (the starting cell is not specified)
    You go to the left by 28 cells( -14 & -14)

    It all depends on where you start from as to where you are.
    Specifying the start cell, in the code, would probably set things right.

    '---
    NLtL
    https://1drv.ms/u/s!Au8Lyt79SOuhZ_zj8wEtOjDcqAI?e=MrzfpA
    Calculate Payments, Custom_Functions, Professional_Compare, Add_Table of Contents

    0 comments