how to find first and last instance

[elmnas]

Hello guys,


I have made following code

that check several statements..

if the value in column "H" have value"MLY" then check column if value is "I" if the value is "MLY" and if the value is more 0 in column "B" and "AB" same row.


For the same row but column "A" make a string of the value (myFound)

now my question:

from the value I want to find all occurrences in same column and make them a color.

see code

Sub mytest()




For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    If Cells(i, "H") = "MLY" And Cells(i, "I") = "MLY" And Len(Cells(i, "B")) <> 0 And Cells(i, "AB") <> 0 Then
   
   
   myFound= Cells(i, "A").Value
 
   End If
    
   Next i
   


End Sub

Could someone help me?

Thank you in advance

 

[CalcSux78]

You could do so with conditional formatting. Are you looking for a VBA approach to accomplish the same thing?

 

[elmnas]

You could do so with conditional formatting. Are you looking for a VBA approach to accomplish the same thing?

I looking for a vba solution

 

[kashif.special2005]

Hi elmnas,

Sub mytest()


For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    If Cells(i, "H") = "MLY" And Cells(i, "I") = "MLY" And Len(Cells(i, "B")) <> 0 And Cells(i, "AB") <> 0 Then
   
   'myFound = Cells(i, "A").Value
   Cells(i, "A").Interior.Color = RGB(246, 160, 126)
 
   End If
    
   Next i


End Sub

Note:- Function:-RGB(Red,Green,Blue)

To Find Red,Green and Blue value for your color, go to Home -> Fill Color (Under Font Pane) -> More Colors -> Custom. and then select your color and find below the Red, Green and Blue number value for your color.

Thanks
Kashif

 

[elmnas]

Hi elmnas,

Sub mytest()


For i = 2 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    If Cells(i, "H") = "MLY" And Cells(i, "I") = "MLY" And Len(Cells(i, "B")) <> 0 And Cells(i, "AB") <> 0 Then
   
   'myFound = Cells(i, "A").Value
   Cells(i, "A").Interior.Color = RGB(246, 160, 126)
 
   End If
    
   Next i


End Sub

Note:- Function:-RGB(Red,Green,Blue)

To Find Red,Green and Blue value for your color, go to Home -> Fill Color (Under Font Pane) -> More Colors -> Custom. and then select your color and find below the Red, Green and Blue number value for your color.

Thanks
Kashif

this is not what I am looking for........