VBA update and clear the value without delay

  • Hi,

    I am having the following code for update and clear the value

    Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim a As Range, rng As Range     Dim counter As Long     Set rng = Sheets("Data").Range("C30:C187")     For Each a In rng         If (Sheets("Data").Range("C6") = "CP18" Or Sheets("Data").Range("C6") = "CP18 TM" Or Sheets("Data").Range("C6") = "M21Z" Or Sheets("Data").Range("C6") = "M25Z") _         And (a > 2.25 Or a < -2.25) Then             counter = counter + 1             a.Offset(0, 0).Interior.ColorIndex = 44             Sheets("Data").Range("H" & a.Row) = "Error "                    Else             a.Offset(0, 0).Interior.ColorIndex = 0             Sheets("Data").Range("H" & a.Row) = ""                     End If             Next a End Sub

    In this the cell C value >2.25 & <-2.25, then the cell C color will change and in H the "Error" will update. And if the condition is not meet then the color should remove from C and Error should remove from H.

    For example C16 value is >2.25 & <-2.25 then the C16 color will change and in H16 it will update "Error". If C16 value not in >2.25 & <-2.25 then the color will remove and "Error" also to be remove from H16.

    At present the above code when the (Sheets("Data").Range("H" & a.Row) = "" )code is enabled then it is check all the row from 30 to 187 and it very delay to update.

    Can any one please help me where the mistake is the code and how to rectify it.

    For your reference the file is attached with this.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!