Macro to find coloured cells and remove #N/A

gusrob - Sep 16, 2009 at 02:45 PM
 gusrob - Sep 17, 2009 at 11:05 AM

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 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 16, 2009 at 09:48 PM
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.
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.