Excel 2003 Macro. Waiting for answer?

Question

I want to write a Macro program, it should compare the values in columnB to cell J1 and K1. if the value in column B is either smaller than J1 or larger than K1, highlight the cell with red. at the end, in the first empty cell in column B, print out the number of red cells.

Would anyone please help me? based on what i wrote, all the cells in column B is highlighted:(

thank you

FJ

Answer 1

You don't need a macro to do this.

Consider the following:

In Col C2 (assuming it is available) put this formula and copy it down to all the rows which have values in column B. This will put the value of 1 when the conditions are met or 0 if the condition is not met.

=if(or(b2<$j$1,b2>$k$1),1,0)

In cell B1 put this formula:

=sum(c:c)

You can then use the value of 1 in column C to do conditional formatting to turn the cells to red.

Hope this helps, good luck and have fun.

Answer 2

Here's how I'd go about it:

Sub MyMacro()

    Dim x As Range

    Dim MyRange As Range

    Dim MyCounter As Long

    Dim y As Long

    

    Set MyRange = Intersect([B:B], ActiveSheet.UsedRange)

    

    MyCounter = 0

    

    For Each x In MyRange

        If x.Value < [J1] Or x.Value > [K1] Then

            MyCounter = MyCounter + 1

            x.Interior.ColorIndex = 3

        End If

    Next x

    

    y = ActiveSheet.UsedRange.Row

    y = y + ActiveSheet.UsedRange.Rows.Count

    Cells(y, 2).Value = MyCounter

    

End Sub

I tried the macro and it works.

Answer 3

Here's the macro:

http://www.savefile.com/files/1751730