Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-22-2018, 12:42 AM
daiwuliz daiwuliz is offline VBA to highlight specific values in the spreadsheet Windows 10 VBA to highlight specific values in the spreadsheet Office 2016
Novice
VBA to highlight specific values in the spreadsheet
 
Join Date: May 2018
Posts: 3
daiwuliz is on a distinguished road
Default 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!
Attached Files
File Type: xlsx Highlight values.xlsx (9.4 KB, 18 views)
Reply With Quote
  #2  
Old 05-22-2018, 02:18 AM
ArviLaanemets ArviLaanemets is offline VBA to highlight specific values in the spreadsheet Windows 8 VBA to highlight specific values in the spreadsheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Look at sheet Example!

Two conditional Formatting rules are needed.
Attached Files
File Type: xlsx ConditionalFormatting.xlsx (12.0 KB, 18 views)
Reply With Quote
  #3  
Old 05-22-2018, 05:09 AM
daiwuliz daiwuliz is offline VBA to highlight specific values in the spreadsheet Windows 10 VBA to highlight specific values in the spreadsheet Office 2016
Novice
VBA to highlight specific values in the spreadsheet
 
Join Date: May 2018
Posts: 3
daiwuliz is on a distinguished road
Default

Perfect! that does exactly what I expect.
Was that done using conditional formatting?
Can it be done using VBA?

Thanks
Reply With Quote
  #4  
Old 05-22-2018, 05:43 AM
ArviLaanemets ArviLaanemets is offline VBA to highlight specific values in the spreadsheet Windows 8 VBA to highlight specific values in the spreadsheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by daiwuliz View Post
Can it be done using VBA?
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.
Reply With Quote
  #5  
Old 05-22-2018, 06:13 PM
p45cal's Avatar
p45cal p45cal is offline VBA to highlight specific values in the spreadsheet Windows 10 VBA to highlight specific values in the spreadsheet Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by daiwuliz View Post
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.
Reply With Quote
  #6  
Old 05-23-2018, 07:49 AM
daiwuliz daiwuliz is offline VBA to highlight specific values in the spreadsheet Windows 10 VBA to highlight specific values in the spreadsheet Office 2016
Novice
VBA to highlight specific values in the spreadsheet
 
Join Date: May 2018
Posts: 3
daiwuliz is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
Look at sheet Example!

Two conditional Formatting rules are needed.
Thank you! Works great
Reply With Quote
  #7  
Old 05-23-2018, 10:30 AM
p45cal's Avatar
p45cal p45cal is offline VBA to highlight specific values in the spreadsheet Windows 10 VBA to highlight specific values in the spreadsheet Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by daiwuliz View Post
What would need to change in the formula if the values were text, not numbers?
(We seem to have lost that question)
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
Reply With Quote
Reply

Thread Tools
Display Modes


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
VBA to highlight specific values in the spreadsheet Macro to copy specific columns in Excel from another spreadsheet KD999 Excel Programming 1 07-20-2012 08:58 AM
VBA to highlight specific values in the spreadsheet 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:25 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft