Need a macro that will find and highlight certain cells

Pandrade

New Member
Joined
Apr 13, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
Hello,
I need a VBA macro that will find and highlight cells in the salary columns that have values less than 10,000 and find and highlight values in the quantity columns that are more than 100 or less than 1. Can someone help me with this? The columns look like this but there are more than a thousand columns:
1586981932021.png
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Pandrade

New Member
Joined
Apr 13, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
I need a VBA macro that will look through the columns automatically because I have about 2,000 columns.
 

Pandrade

New Member
Joined
Apr 13, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
The difficulty with this is that the salary and quantity columns alternate and there are thousands of columns.
 

snjpverma

Well-known Member
Joined
Oct 2, 2008
Messages
1,383
Office Version
  1. 2016
Platform
  1. Windows
Try this code in a copy of your workbook as VBA doesn't have Undo option and I am a beginner in VBA.

VBA Code:
Sub highlightrecords()
Dim lastrow As Long

lastrow = Cells.Find(What:="*", _
            After:=Range("A1"), _
            LookAt:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row

Selection.AutoFilter
Range("A1").EntireRow.Find("Salary").Select
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:="<10000"
Range(ActiveCell.Offset(1), Cells(lastrow, ActiveCell.Column)).Interior.Color = rgbGreen

Selection.AutoFilter
Range("A1").EntireRow.Find("Quantity").Select
Selection.AutoFilter Field:=ActiveCell.Column, Criteria1:=">100", Operator:=xlOr, Criteria2:="<1"
Range(ActiveCell.Offset(1), Cells(lastrow, ActiveCell.Column)).Interior.Color = rgbGreen

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,188
Messages
5,546,467
Members
410,741
Latest member
Count25
Top