elsuji
09-23-2019, 11:18 AM
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 " '& counter
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.
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 " '& counter
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.