Setting Reference as dynamic range from inactive sheet

cbutters

New Member
Joined
Aug 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello all I am hitting a brick wall. I am trying to create a reference based off of a dynamic range, however if the sheet is not active I keep getting an error for out of scope or application-defined or object-defined error. The dynamic range is made through special cells since the data often has rows and cells that are empty. I am creating 2 references so that I can do a conditional format to see the changes (has it's own issues with highlighting the empty cells) If I set the sheet to active, it works perfectly.. set sheet 2 to active and it fails... I'm pulling my hair out please help!

VBA Code:
Sub SelectRange1()


With Worksheets("Table 1")
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Sheets("Table 1")
Set StartCell = Range("A1")

'Refresh UsedRange
  Sheets("Table 1").UsedRange

'Find Last Row and Column
  LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
  LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column

'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
  Application.Goto Reference:="Sheet1Data"
End With

End Sub

VBA Code:
Sub Macro2()

' conditional formatt to highlight unique
'
     Application.Goto Reference:="Sheet1Data"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF(Sheet2Data,A1)=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top