Macro to find coloured cells and remove #N/A

 gusrob -

I hope you can assist. I have two questions.

Firstly, I need to have a marco that locates a coloured cell in column "a" and if found, colours the entire row the same colour.

Secondly, I need a macro that removes any #n/a found after all the data in the sheet and ignores any #n/a within the data.

i look forward to your reply. Thanks


4 replies

Registration date
Sunday June 14, 2009
Last seen
August 7, 2021
question 1
try this macro (modify to suit you)

Sub test()
Dim rng As Range, c As Range, j As Integer
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In rng
If c.Interior.ColorIndex <> xlNone Then
j = c.Interior.ColorIndex
c.EntireRow.Interior.ColorIndex = j
End If
Next c
End Sub

question no. 2

can I give a solution without a macro
choose "special" at the bottom left
in the "goto special" window
choose the radio button "constants"
uncheck the button
numbers, text and logicals
keep tick only in errors
click ok
now all the cells with error(#N/A) will be highlightd
without selecting any other cell
click edit(menu)-clear-clearall
do you get what you want.
this will remove other error cells also like"value" or "#DIV/0!" etc.
if your really want a macro then RECORD a macro taking the above steps and suitably modify the macro.
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Hi, Thanks.

The first color macro worked great, but the second suggestion not so. As this function selects all the errors within the entire data range, I'm only wanting to select any errors that occur outside the data it self. I'll explain, the data range is not constant, Column "B" is the most important piece of information. I have other columns which run longer than coloum "B", but if there is no data in "A" or "B" then a error will occur in other columns, so i need to delete these errors, but i do not want to delete any errors that may occur in the effective data range.
I hope this helps.
Just use IF function.

If column A = "" then "" else if column B= "" then "" else TheFormulaYourUsing.


Best regards,
Cool Thanks,

I didn't even think of an IF formula - was trying to do it with VB formula, but couldn't get it right.