VBA to highlight specific values in the spreadsheet

p45cal · #5 05-22-2018, 06:13 PM

Quote:

Originally Posted by daiwuliz

Can it be done using VBA?

Try this code when the sheet in question is the active sheet:

Code:

Sub blah()
For Each cll In Columns("A:A").SpecialCells(xlCellTypeConstants, 1).Cells
  Row1Addr = cll.Offset(1, 2).Resize(, 10).Address(False, True)
  With cll.Offset(1, 2).Resize(4, 10).FormatConditions
    .Delete
    With .Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & cll.Address)
      With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
      End With
    End With
  End With

  With cll.Offset(1, 1).Resize(4).FormatConditions
    .Delete
    With .Add(Type:=xlExpression, Formula1:="=NOT(ISERROR(MATCH(" & cll.Address & "," & Row1Addr & ",0)))")
      With .Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
      End With
    End With
  End With
Next cll
End Sub

It relies on the positional relationship between the cells containing numbers in column A; it puts conditional formatting (a) in a range 4 rows by 10 columns whose top left corner is one cell below and 2 cells to the right of each cell with a number in column A, and (b) a range 4 rows by 1 columns, offset 1 cell down and 1 cell to the right of that same cell in column A.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to highlight lines containing specific words	SixStringSW	Word VBA	4	06-03-2018 03:57 PM
Trying to import specific data from one spreadsheet to another, without matching rows	Wynka	Excel	0	11-26-2014 09:33 AM
Macro to copy specific columns in Excel from another spreadsheet	KD999	Excel Programming	1	07-20-2012 08:58 AM
Looking for a specific spreadsheet	DivideByZer0	Excel	3	11-10-2009 05:58 PM
find - reading highlight - highlight all / highlight doesn't stick when saved	bobk544	Word	3	04-15-2009 03:31 PM