Need Help in This VBA

[Prasad K]

i have applied this below vba to highlight cell color based on cell value (Pending) it's working when i am typing Pending in cell value of Column C Range & here i have created a drop down list with text Pending in Column C Range & when i selected Pending from drop down then the cell is not highlighting

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
    With Sheet1
    For Each rCell In Range("C1:C100")
        If rCell.Value = "Pending" Then
            rCell.Interior.Color = vbRed
        Else
            rCell.Interior.Color = xlNone
    End If
    Next
    End With
End Sub

 

[footoo]

It would be better to use conditional formatting.

 

[Alex Blakenburg]

i have applied this below vba to highlight cell color based on cell value (Pending) it's working when i am typing Pending in cell value of Column C Range & here i have created a drop down list with text Pending in Column C Range & when i selected Pending from drop down then the cell is not highlighting

Normally I would expect the code to look more like the below.

Having said that, it would not explain why it doesn't work when you select Pending from a dropdown.
The most likely issue is that the dropdown value is not exactly equal to Pending. Try typing =Len(Cell_Containing_Pending) and check that it returns 7 (and that there are no spaces in there)

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False

    If Not Intersect(Target, Range("C1:C100")) Is Nothing Then

        If Target.Value = "Pending" Then
            Target.Interior.Color = vbRed
        Else
            Target.Interior.Color = xlNone
        End If

    End If
    
    Application.EnableEvents = True
End Sub

 

[Prasad K]

Normally I would expect the code to look more like the below.

Having said that, it would not explain why it doesn't work when you select Pending from a dropdown.
The most likely issue is that the dropdown value is not exactly equal to Pending. Try typing =Len(Cell_Containing_Pending) and check that it returns 7 (and that there are no spaces in there)

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Application.EnableEvents = False

    If Not Intersect(Target, Range("C1:C100")) Is Nothing Then

        If Target.Value = "Pending" Then
            Target.Interior.Color = vbRed
        Else
            Target.Interior.Color = xlNone
        End If

    End If
   
    Application.EnableEvents = True
End Sub

it's my mistake Alex

i have checked now in my data validation i have given space in (source Pending , Completed) & now my code is working i got that vba code in google search of stackoverflow
and i didn't applied your code in my worksheet module i will try it now

 

[Alex Blakenburg]

Glad removing the space fixed the problem.
Let us know how you go with alternative code.

Your current code is going to run through a loop of all 100 cells in column C regardless of what was changed in the sheet eg any change anywhere on the sheet.
Typically you test to see if the cell you changed meets the criteria, in this case whether the Cell changed is within the range of C1:C100 and if it is you perform a certain action.

 

[Prasad K]

Typically you test to see if the cell you changed meets the criteria, in this case whether the Cell changed is within the range of C1:C100 and if it is you perform a certain action

Yes I tested again that code if it will work or not

And Thankyou so much for written a code i have tested yours it's working perfectly

 

[Alex Blakenburg]

Glad I could help. Thank for letting me know.