how to find first and last instance

elmnas

Board Regular
Joined
Feb 20, 2015
Messages
206
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

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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
You could do so with conditional formatting. Are you looking for a VBA approach to accomplish the same thing?
 

kashif.special2005

Active Member
Joined
Oct 26, 2009
Messages
435
Hi elmnas,

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
   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

Board Regular
Joined
Feb 20, 2015
Messages
206
Hi elmnas,

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
   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........
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,857
Messages
5,507,756
Members
408,647
Latest member
Nicho la zido

This Week's Hot Topics

Top