How can I correct this ActiveCell.Offset issue?

Question

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

Answer 1

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