#1
|
|||
|
|||
VBA to highlight specific values in the spreadsheet
Hello,
Could you please help. I have attached the spreadsheet where I am trying to achieve the following: Based on value in A5, highlight cells in the spreadsheet that equal to that value as well as cell in column B where row contains that value. Thanks! |
#2
|
|||
|
|||
Look at sheet Example!
Two conditional Formatting rules are needed. |
#3
|
|||
|
|||
Perfect! that does exactly what I expect.
Was that done using conditional formatting? Can it be done using VBA? Thanks |
#4
|
|||
|
|||
Probably.
I myself use VBA in Excel when: 1. I need an UDF when I can't get the result using worksheet functions (like getting name of n'th file of certain type in certain folder); 2. I need an Open event (e.g. to update parameters of ODBC query to same Excel workbook when the workbook is moved or renamed); 3. The Excel workbook is used as tool to read some data from one source of certain format, and to create another file of another format based on read data. In any other cases, when there will be need for VBA, I will seriously consider Access or SQL Server instead. |
#5
|
||||
|
||||
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 |
#6
|
|||
|
|||
Thank you! Works great
|
#7
|
||||
|
||||
Quote:
Based on Text and Numbers in column B: Code:
Sub blah2() For Each are In Columns(2).SpecialCells(xlCellTypeConstants, 3).Areas Row1Addr = are.Cells(1).Offset(, 1).Resize(, 10).Address(False, True) With are.Cells(1).Offset(, 1).Resize(4, 10).FormatConditions .Delete With .Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & are.Cells(1).Offset(-1, -1).Address) With .Interior .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 End With End With End With With are.FormatConditions .Delete With .Add(Type:=xlExpression, Formula1:="=NOT(ISERROR(MATCH(" & are.Cells(1).Offset(-1, -1).Address & "," & Row1Addr & ",0)))") With .Interior .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 End With End With End With Next are End Sub |
|
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 |