Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Cells that contain Matches to Data in a List
The small procedure below works perfectly at changing the format of
any number of cells that contain data that matches that in a few specific cells. Sub FormatMatchingDates() Dim c As Range For Each c In Sheet1.Range("A1:BB78") If c.Value = Date1 Or c.Value = Date2 Or c.Value = Date3 Then With c .Font.Size = 10 .Font.Bold = True .Interior.ColorIndex = 4 End With End If Next Exit Sub End Sub However, the problem has changed. I now have almost 300 dates to match. They are arranged in a single column so I can find the matches with a VLookup formula in a column next to my test data but that just isn't a good solution. It really needs to be highlighted with formatting. There are some blank cells among the "c" in Sheet1.Range("A1:BB78"). I tried something with Match to test it for being TRUE rather than using the location it returns, but it didn't work for me. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Cells that contain Matches to Data in a List
Something like (untested):
Sub FormatMatchingDates() Dim c As Range, cDates As Range 'cDates is the column of 300 dates Set cDates = Sheet2.Range("D1:D300") 'or whatever location For Each c In Sheet1.Range("A1:BB78") If Not cDates.Find(c) Is Nothing Then With c .Font.Size = 10 .Font.Bold = True .Interior.ColorIndex = 4 End With End If Next Exit Sub End Sub -- Vasant "John Pierce" wrote in message om... The small procedure below works perfectly at changing the format of any number of cells that contain data that matches that in a few specific cells. Sub FormatMatchingDates() Dim c As Range For Each c In Sheet1.Range("A1:BB78") If c.Value = Date1 Or c.Value = Date2 Or c.Value = Date3 Then With c .Font.Size = 10 .Font.Bold = True .Interior.ColorIndex = 4 End With End If Next Exit Sub End Sub However, the problem has changed. I now have almost 300 dates to match. They are arranged in a single column so I can find the matches with a VLookup formula in a column next to my test data but that just isn't a good solution. It really needs to be highlighted with formatting. There are some blank cells among the "c" in Sheet1.Range("A1:BB78"). I tried something with Match to test it for being TRUE rather than using the location it returns, but it didn't work for me. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Cells that contain Matches to Data in a List
Vasant,
Thanks for your help. It does the job perfectly! John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Cells that contain Matches to Data in a List
You're welcome, John!
-- Vasant "John Pierce" wrote in message om... Vasant, Thanks for your help. It does the job perfectly! John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data from another sheet that matches to a class list? | Excel Worksheet Functions | |||
If a cell matches a list of values...... | Excel Discussion (Misc queries) | |||
Apply Conditional Formatting If Data in More Than 1 Cell Matches | Excel Discussion (Misc queries) | |||
Count cells w/values in column if the data in column a matches cri | Excel Worksheet Functions | |||
how do i omit the a list, and it's matches, so neither exists? | Excel Worksheet Functions |