Highlight rows if cell equals Zero.

[countryfan_nt]

Hello friends,

I have a sheet with data, I want the below macro to go thru all the cells of column AF. If it finds 0.00 in the cells of AF, then I want the entire row to be highlighted (selected).

Please help me fill the question marks in the below code. And see if please see if I missed anything in the code.

Thanks,
Nawaf

Sub Macro1()
    Dim Curr_Cell As Long
    Dim Prev_Cell As Long
    Dim i As Long
    Dim Loop_Count As Long
    
    ActiveWindow.Activate
    Loop_Count = Range(Selection, Selection.End(xlDown)).Count
    'MsgBox Loop_Count
    For i = 2 To Loop_Count
        Range("AF" & i).Select
        Curr_Cell = ActiveWindow.ActiveCell
        'MsgBox Curr_Cell
        If (Curr_Cell <> Prev_Cell) Then
        ??????
        End If
        Prev_Cell = Curr_Cell
       Next i
End Sub

 

[Greg Truby]

Just use conditional formatting. Select Formula and =AND($AF1=0,$AF1<>"") (assuming the active cell is in row 1).

edited to make column absolute and to handle blank cells.

 

[Norie]

What are you actually trying to do?

ie why are you highlighting/selecting the rows?

I think there could be other ways of doing this without having to loop.

 

[countryfan_nt]

I am selecting the rows to enable me to see what I want to delete, ie I will delete all the rows with 0.00. Conditional formatting will only show me what needs to be deleted, but the code that I am working on will ease the deleting process.

Your help is appreciated.

Nawaf

 

[Norie]

Nawaf

I'm sorry but I don't understand.

Why do you need to highlight/select the rows?

Can't you just delete them as you go along?

 

[countryfan_nt]

Thanks Norie for replying,

It would take a long time since I have data in over 3000 rows, once the rows are highlighted/selected it would be easy for me to delete.

Right click and delete.

Your help is highly regarded,
Nawaf

 

[Greg Truby]

Maybe I'm just dumber'n the average bear...for I would simply either (a) use AutoFilter or (b) Sort Decending, do a FIND on column AF for 0 and delete from there down. If I needed to restore original order or something then I might drag VBA into it.

 

[Norie]

Nawaf

Are you sure that's the case?

If speed is an issue you should be avoiding Select in the first place.

You might also want to look into using Autofilter.

By the way I don't see anything in the posted code that checks for 0.00.

 

[countryfan_nt]

It is funny how sometimes I complicate things, You guys are right I guess I don't need VBA after all, Greg/Norie you are right.

I will sort the data instead.

Thank you all.
Nawaf