Excel Font Color Macro 3

[tdpman]

Anyone have a piece of code that will scan column A for any cells with a value of #N/A and change the font color of that entire row to RED?

Thanks for your help!

 

[DaleWatson123321]

I created and tested the following, and it does what you've described.

Sub Set_Colors()
Application.ScreenUpdating = False
Set_Data
Color_Rows
Application.Goto Reference:="R1C1"
Application.ScreenUpdating = True
End Sub

Sub Set_Data()
'Sets range name "data"
Application.Goto Reference:="R1C1"
FirstCell = ActiveCell.Address
LastCell = [A65536].End(xlUp).Address
rng = FirstCell & ":" & LastCell
Range(rng).Name = "data"
End Sub

Sub Color_Rows()
'Colors each row RED, based on value
'in Column A being ERROR - #DIV/0! or #N/A
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
For Each c In Range("data&quot
If IsError(c.Value) Then
c.EntireRow.Select
Selection.Font.ColorIndex = 3
Else
c.EntireRow.Select
Selection.Font.ColorIndex = 0
End If
ActiveCell.Offset(1, 0).Select
Next
Application.CutCopyMode = True
End Sub


I hope this is what you wanted. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

P.S.

The second last line -
"Application.CutCopyMode = True"
...is NOT required. You can delete this, (or leave it, as it won't cause any problem).

 

[tdpman]

That is awesome, works great!!!! Thanks a bunch!

 

[Zathras]

Sorry for the intrusion, I hardly ever can find fault with Dale's code, but in this case I must interject. The macro does not have to be nearly so involved. This should do the job quite nicely. I leave it to tdpman to decide which is easier to understand and use.

Option Explicit

Sub Flag_ColumnA_NA()
Dim rngWork As Range
Dim c As Range
  Set rngWork = Intersect(Range("A1:A65536"), ActiveSheet.UsedRange)
  For Each c In rngWork
    If IsError(c.Value) Then
      c.EntireRow.Font.ColorIndex = 3
    Else
      c.EntireRow.Font.ColorIndex = 0
    End If
  Next c
  Set rngWork = Nothing
End Sub

 

[KenWright]

I know you asked for a piece of code and Dale has already given that to you, but just in case it is of any use, you may also like the following:-

Assuming your rows of data run from Col A to Col Z (Or whatever). Select A1:Z1 and do Format / Conditional Formatting / change 'cell value is' to 'Formula is' and then type in =ISNA($A1), click on format and then select a red font (Why not have a nice pale yellow background as well while you're at it) and then it OK till out. With the same range still selected click on the format painter (Little yellow paintbrush) and then drag the cursor over the column headings A to Z, or just drag it over all your data in your relevant columns. Now it will automatically change colour anytime you get a #N/A without having to run a macro.

One other thing you could do as well is for example in cell A1, put =SUM(A2:A1000) or whatever range you have, and then if you get a #N/A anywhere in Col A you will know instantly without having to scroll down, because Row 1 will light up for you as the Sum formula will also return #N/A if any part of the range being summed has that error.

Regards
Ken...............