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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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,106,545
Messages
5,511,962
Members
408,871
Latest member
Usman21

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top