Conditional Formatting for column using Match and named range

[seagreen]

Hello,
Is there a way to "copy down" for conditional formatting in a column? For example, I have the following rule for conditional formatting for a cell: =MATCH($A2,myNamedRange,0)

I have a column of over 7000 cells, I would like to be able to conditionally format for each of cells where each cell in column A is matched against all values in the named range. If the cell matches one of the values in the named range, I'd like the cell to have a colored fill.

The question: how do I increment the first value ($A2 in my example) ?

Is this something that can only be done using VBA?

Thanks in advance for your time and help.

 

[mvptomlinson]

If I'm understanding you correctly, you just have to select the once cell that has conditional formatting, then click Edit -> Copy.

Select the other 7000 cells in the column, then click Edit -> PasteSpecial.. -> Formats -> OK.

 

[seagreen]

Thanks mvptomlinson. Doh, I didn't think of that.

 

[Chris Bode]

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:Calibri; mso-font-alt:"Century Gothic"; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:Calibri; mso-fareast-font-family:Calibri; mso-bidi-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Suppose you have a name list then
1.Select a cell in column A (say A1) > right click>conditional formatting
2.Fromthe dialog box select cell value is from left drop down list, equal to from middle drop down and enter following formula is the box to the right
=VLOOKUP(A1,Names,1,FALSE)
3.Click on format button and choose a color
Now you get the cell colored if its value is present in names list
Similarly you can apply the same process to other cells

 

[seagreen]

Thanks Chris! Is using VLOOKUP preferable to using MATCH for this application? MATCH worked for me but I would like to learn a better way...

 

[Aladin Akyurek]

Thanks Chris! Is using VLOOKUP preferable to using MATCH for this application? MATCH worked for me but I would like to learn a better way...

MATCH is appropriate here, it might even be a tad faster.