VBA to Highlight #N/A in dynamic range dataset

[TheHack22]

Hi VBA Experts.
I have looked everywhere but can't seem to find a VBA Sub to find and highlight #N/A in a dataset if any exist.
The dataset is hardcoded data formatted as general. I even try to record a macro to do this, but the recorder doesn't record the "Find & Replace" box activities.

Any help with this is greatly appreciated.
Imran

 

[JamesCanale]

Must it be VBA? A nice conditional formatting formula with

MrExcelPlayground6.xlsx
	H
5	5
6	#N/A
7	3
8	4
9	#N/A
Sheet12

Cell Formulas
Range	Formula
H6,H9	H6	=NA()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H5:H9	Expression	=ISNA(H5)	text	NO

 

[Fluff]

This will highlight all error values, not just #N/A

Sub Imran()
   On Error Resume Next
   ActiveSheet.UsedRange.SpecialCells(xlConstants, xlErrors).Interior.Color = 255
   On Error GoTo 0
End Sub

 

[TheHack22]

Must it be VBA? A nice conditional formatting formula with

MrExcelPlayground6.xlsx
	H
5	5
6	#N/A
7	3
8	4
9	#N/A
Sheet12

Cell Formulas
Range	Formula
H6,H9	H6	=NA()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H5:H9	Expression	=ISNA(H5)	text	NO

@JamesCanale

Thanks for your response. I would like to have this done VIA VBA. Because this is not a static file, every day a new consolidated data file is created from scratch(May have "#N/A"). It will be very "time-consuming and tedious" to do this every time. All the manual processes add up. I have automated 99% of the manual steps for this daily process. This is the last one I'm trying to automate.

 

[TheHack22]

This will highlight all error values, not just #N/A

Sub Imran()
   On Error Resume Next
   ActiveSheet.UsedRange.SpecialCells(xlConstants, xlErrors).Interior.Color = 255
   On Error GoTo 0
End Sub

@Fluff
This is beautiful - did exactly what I wanted it to do.
I have searched so many places and couldn't find an answer. You're awesome. You saved me a lot of headaches.
Imran

 

[Fluff]

Glad we could help & thanks for the feedback.