Conditional format help

LuGyver

Board Regular
Joined
Mar 13, 2014
Messages
88
Office Version
  1. 2007
Platform
  1. Windows
Hello all and thank you for your help.
I am trying to highlight any cell in C1:L24 that match the number in column B with relation to 4-days-before the given date, 4-days-after the date, and include the given-date. So for 8/3/2014 in column B "022", the conditional format would highlight any match in C14:L22.
In this example there may be other matches not highlighted--- this is just for an example.
I highlight the cells but in here I could only light up the text
First I select the range C14:L22 then
I use based on a function =IF(COUNTIF($B8773:$B8781,F8773),COUNTIF($B8773:$B8781,F8773),"") and it works.

but if I change it to =IF(COUNTIF($B8773:$B8781,F8777),COUNTIF($B8773:$B8781,F8777),"") It highlights rows/columns within C:L which is strange.
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]-->
A
B
C
D
E
F
G
H
I
J
K
L
[FONT=&quot]7/17/2014[/FONT]
[FONT=&quot]639[/FONT]
[FONT=&quot]297[/FONT]
[FONT=&quot]230[/FONT]​
[FONT=&quot]708[/FONT]​
[FONT=&quot]618[/FONT]​
[FONT=&quot]194[/FONT]
[FONT=&quot]165[/FONT]​
[FONT=&quot]538[/FONT]​
[FONT=&quot]917[/FONT]​
[FONT=&quot]895[/FONT]​
[FONT=&quot]296[/FONT]

[FONT=&quot]7/18/2014[/FONT]​
[FONT=&quot]168[/FONT]​
[FONT=&quot]665[/FONT]​
[FONT=&quot]847[/FONT]​
[FONT=&quot]075[/FONT]​
[FONT=&quot]336[/FONT]​
[FONT=&quot]982[/FONT]
[FONT=&quot]938[/FONT]​
[FONT=&quot]148[/FONT]​
[FONT=&quot]582[/FONT]​
[FONT=&quot]437[/FONT]​
[FONT=&quot]987[/FONT]

[FONT=&quot]7/19/2014[/FONT]​
[FONT=&quot]922[/FONT]​
[FONT=&quot]954[/FONT]​
[FONT=&quot]488[/FONT]​
[FONT=&quot]256[/FONT]​
[FONT=&quot]066[/FONT]​
[FONT=&quot]377[/FONT]
[FONT=&quot]393[/FONT]​
[FONT=&quot]680[/FONT]​
[FONT=&quot]972[/FONT]​
[FONT=&quot]262[/FONT]​
[FONT=&quot]690[/FONT]

[FONT=&quot]7/20/2014[/FONT]​
[FONT=&quot]110[/FONT]​
[FONT=&quot]064[/FONT]​
[FONT=&quot]812[/FONT]​
[FONT=&quot]512[/FONT]​
[FONT=&quot]204[/FONT]​
[FONT=&quot]385[/FONT]
[FONT=&quot]330[/FONT]​
[FONT=&quot]438[/FONT]​
[FONT=&quot]407[/FONT]​
[FONT=&quot]108[/FONT]​
[FONT=&quot]943[/FONT]

[FONT=&quot]7/21/2014[/FONT]​
[FONT=&quot]476[/FONT]​
[FONT=&quot]196[/FONT]​
[FONT=&quot]208[/FONT]​
[FONT=&quot]576[/FONT]​
[FONT=&quot]831[/FONT]​
[FONT=&quot]671[/FONT]
[FONT=&quot]682[/FONT]​
[FONT=&quot]113[/FONT]​
[FONT=&quot]054[/FONT]​
[FONT=&quot]499[/FONT]​
[FONT=&quot]262[/FONT]

[FONT=&quot]7/22/2014[/FONT]​
[FONT=&quot]926[/FONT]​
[FONT=&quot]207[/FONT]​
[FONT=&quot]980[/FONT]​
[FONT=&quot]060[/FONT]​
[FONT=&quot]843[/FONT]​
[FONT=&quot]362[/FONT]
[FONT=&quot]943[/FONT]​
[FONT=&quot]166[/FONT]​
[FONT=&quot]401[/FONT]​
[FONT=&quot]040[/FONT]​
[FONT=&quot]551[/FONT]

[FONT=&quot]7/23/2014[/FONT]​
[FONT=&quot]235[/FONT]​
[FONT=&quot]564[/FONT]​
[FONT=&quot]043[/FONT]​
[FONT=&quot]763[/FONT]​
[FONT=&quot]758[/FONT]​
[FONT=&quot]914[/FONT]
[FONT=&quot]455[/FONT]​
[FONT=&quot]282[/FONT]​
[FONT=&quot]590[/FONT]​
[FONT=&quot]557[/FONT]​
[FONT=&quot]557[/FONT]

[FONT=&quot]7/24/2014[/FONT]​
[FONT=&quot]728[/FONT]​
[FONT=&quot]981[/FONT]​
[FONT=&quot]251[/FONT]​
[FONT=&quot]646[/FONT]​
[FONT=&quot]549[/FONT]​
[FONT=&quot]635[/FONT]
[FONT=&quot]537[/FONT]​
[FONT=&quot]830[/FONT]​
[FONT=&quot]591[/FONT]​
[FONT=&quot]345[/FONT]​
[FONT=&quot]211[/FONT]

[FONT=&quot]7/25/2014[/FONT]​
[FONT=&quot]858[/FONT]​
[FONT=&quot]997[/FONT]​
[FONT=&quot]428[/FONT]​
[FONT=&quot]940[/FONT]​
[FONT=&quot]377[/FONT]​
[FONT=&quot]115[/FONT]
[FONT=&quot]424[/FONT]​
[FONT=&quot]116[/FONT]​
[FONT=&quot]836[/FONT]​
[FONT=&quot]173[/FONT]​
[FONT=&quot]585[/FONT]

[FONT=&quot]7/26/2014[/FONT]​
[FONT=&quot]643[/FONT]​
[FONT=&quot]003[/FONT]​
[FONT=&quot]624[/FONT]​
[FONT=&quot]166[/FONT]​
[FONT=&quot]598[/FONT]​
[FONT=&quot]337[/FONT]
[FONT=&quot]046[/FONT]​
[FONT=&quot]754[/FONT]​
[FONT=&quot]880[/FONT]​
[FONT=&quot]407[/FONT]​
[FONT=&quot]049[/FONT]

[FONT=&quot]7/27/2014[/FONT]​
[FONT=&quot]171[/FONT]​
[FONT=&quot]321[/FONT]​
[FONT=&quot]538[/FONT]​
[FONT=&quot]058[/FONT]​
[FONT=&quot]220[/FONT]​
[FONT=&quot]118[/FONT]
[FONT=&quot]475[/FONT]​
[FONT=&quot]228[/FONT]​
[FONT=&quot]026[/FONT]​
[FONT=&quot]047[/FONT]​
[FONT=&quot]568[/FONT]

[FONT=&quot]7/28/2014[/FONT]​
[FONT=&quot]796[/FONT]​
[FONT=&quot]677[/FONT]​
[FONT=&quot]927[/FONT]​
[FONT=&quot]975[/FONT]​
[FONT=&quot]171[/FONT]
[FONT=&quot]122[/FONT]

[FONT=&quot]538[/FONT]​
[FONT=&quot]327[/FONT]​
[FONT=&quot]864[/FONT]​
[FONT=&quot]004[/FONT]​
[FONT=&quot]722[/FONT]

[FONT=&quot]7/29/2014[/FONT]​
[FONT=&quot]032[/FONT]​
[FONT=&quot]244[/FONT]​
[FONT=&quot]670[/FONT]​
[FONT=&quot]979[/FONT]​
[FONT=&quot]324[/FONT]
[FONT=&quot]585[/FONT]
[FONT=&quot]895[/FONT]​
[FONT=&quot]458[/FONT]​
[FONT=&quot]056[/FONT]​
[FONT=&quot]081[/FONT]​
[FONT=&quot]856[/FONT]

[FONT=&quot]7/30/2014[/FONT]​
[FONT=&quot]972[/FONT]
[FONT=&quot]227[/FONT]
[FONT=&quot]187[/FONT]​
[FONT=&quot]267[/FONT]​
[FONT=&quot]479[/FONT]​
[FONT=&quot]847[/FONT]
[FONT=&quot]262[/FONT]​
[FONT=&quot]853[/FONT]​
[FONT=&quot]601[/FONT]​
[FONT=&quot]315[/FONT]​
[FONT=&quot]823[/FONT]

[FONT=&quot]7/31/2014[/FONT]​
[FONT=&quot]388[/FONT]​
[FONT=&quot]033[/FONT]
[FONT=&quot]034[/FONT]​
[FONT=&quot]785[/FONT]​
[FONT=&quot]170[/FONT]​
[FONT=&quot]129[/FONT]
[FONT=&quot]022[/FONT]
[FONT=&quot]421[/FONT]​
[FONT=&quot]725[/FONT]​
[FONT=&quot]529[/FONT]​
[FONT=&quot]383[/FONT]

[FONT=&quot]8/1/2014[/FONT]​
[FONT=&quot]165[/FONT]​
[FONT=&quot]911[/FONT]
[FONT=&quot]326[/FONT]​
[FONT=&quot]732[/FONT]​
[FONT=&quot]290[/FONT]​
[FONT=&quot]980[/FONT]
[FONT=&quot]276[/FONT]​
[FONT=&quot]100[/FONT]​
[FONT=&quot]499[/FONT]​
[FONT=&quot]923[/FONT]​
[FONT=&quot]075[/FONT]

[FONT=&quot]8/2/2014[/FONT]​
[FONT=&quot]033[/FONT]​
[FONT=&quot]856[/FONT]
[FONT=&quot]468[/FONT]​
[FONT=&quot]035[/FONT]​
[FONT=&quot]321[/FONT]​
[FONT=&quot]725[/FONT]
[FONT=&quot]620[/FONT]​
[FONT=&quot]118[/FONT]​
[FONT=&quot]367[/FONT]​
[FONT=&quot]672[/FONT]​
[FONT=&quot]049[/FONT]

[FONT=&quot]8/3/2014[/FONT]​
[FONT=&quot]022[/FONT]​
[FONT=&quot]206[/FONT]
[FONT=&quot]860[/FONT]​
[FONT=&quot]170[/FONT]​
[FONT=&quot]902[/FONT]​
[FONT=&quot]195[/FONT]
[FONT=&quot]786[/FONT]​
[FONT=&quot]509[/FONT]​
[FONT=&quot]864[/FONT]​
[FONT=&quot]524[/FONT]​
[FONT=&quot]022[/FONT]

[FONT=&quot]965[/FONT]
[FONT=&quot]294[/FONT]​
[FONT=&quot]165[/FONT]​
[FONT=&quot]857[/FONT]​
[FONT=&quot]782[/FONT]
[FONT=&quot]146[/FONT]​
[FONT=&quot]368[/FONT]​
[FONT=&quot]676[/FONT]​
[FONT=&quot]709[/FONT]​
[FONT=&quot]423[/FONT]

[FONT=&quot]660[/FONT]
[FONT=&quot]956[/FONT]​
[FONT=&quot]048[/FONT]​
[FONT=&quot]691[/FONT]​
[FONT=&quot]021[/FONT]
[FONT=&quot]551[/FONT]​
[FONT=&quot]373[/FONT]​
[FONT=&quot]689[/FONT]
[FONT=&quot]097[/FONT]​
[FONT=&quot]002[/FONT]

[FONT=&quot]672[/FONT]
[FONT=&quot]022[/FONT]
[FONT=&quot]951[/FONT]​
[FONT=&quot]540[/FONT]​
[FONT=&quot]453[/FONT]
[FONT=&quot]751[/FONT]​
[FONT=&quot]540[/FONT]​
[FONT=&quot]698[/FONT]​
[FONT=&quot]950[/FONT]​
[FONT=&quot]073[/FONT]

[FONT=&quot]029[/FONT]
[FONT=&quot]125[/FONT]​
[FONT=&quot]150[/FONT]​
[FONT=&quot]418[/FONT]​
[FONT=&quot]912[/FONT]
[FONT=&quot]737[/FONT]​
[FONT=&quot]987[/FONT]​
[FONT=&quot]468[/FONT]​
[FONT=&quot]891[/FONT]​
[FONT=&quot]111[/FONT]

[FONT=&quot]023[/FONT]
[FONT=&quot]110[/FONT]​
[FONT=&quot]106[/FONT]​
[FONT=&quot]063[/FONT]​
[FONT=&quot]885[/FONT]
[FONT=&quot]612[/FONT]​
[FONT=&quot]731[/FONT]​
[FONT=&quot]512[/FONT]​
[FONT=&quot]056[/FONT]​
[FONT=&quot]654[/FONT]

[FONT=&quot]390[/FONT]
[FONT=&quot]685[/FONT]​
[FONT=&quot]052[/FONT]​
[FONT=&quot]129[/FONT]​
[FONT=&quot]574[/FONT]
[FONT=&quot]632[/FONT]​
[FONT=&quot]812[/FONT]​
[FONT=&quot]866[/FONT]​
[FONT=&quot]093[/FONT]​
[FONT=&quot]245[/FONT]


<tbody>
</tbody>
<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[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-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;} </style> <![endif]-->Thank You So Much For Your Help
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I would say start in one cell only.. so instead of selecting C1:L24, select C4 only.. then add your conditional formula to that cell alone.. I'm guessing it would be =IF(COUNTIF($B1:$B7,C4),COUNTIF($B1:$B7,C4),"")

Then, copy C4 all the way to L4. If you copy by dragging the plus sign, then select the menu to "fill formatting only," otherwise copy C4 and paste formatting only to the rest of the cells in the row.. that way the conditional formula updates for each cell...

then, copy the row up (formatting only) to row 1, and all the way down.. (fill formatting only)
 
Upvote 0
I would say start in one cell only.. so instead of selecting C1:L24, select C4 only.. then add your conditional formula to that cell alone.. I'm guessing it would be =IF(COUNTIF($B1:$B7,C4),COUNTIF($B1:$B7,C4),"")

Then, copy C4 all the way to L4. If you copy by dragging the plus sign, then select the menu to "fill formatting only," otherwise copy C4 and paste formatting only to the rest of the cells in the row.. that way the conditional formula updates for each cell...

then, copy the row up (formatting only) to row 1, and all the way down.. (fill formatting only)

I know how to fill in a sheet with a formula. This is a Conditional formatquestion
There are thousands of cells and populated already. I can't just fill a formula down over the top of them. I want to "highlight" the cells

Thank you for trying to help
 
Upvote 0
Put the reference data 8/3/2014 in, say, O2

Select C1:L24 being C1 the active cell

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

insert this formula
=AND(AND($A1>=$O$2-4,$A1<=$O$2+4),C1=INDEX($B:$B,MATCH($O$2,$A:$A,0)))

pick the format you want.

Hope this helps

M.
 
Upvote 0
you are welcome...

now.. I said "formatting" not "formula"..

and I tested it to make sure it works before I just blindly gave you an opinion..

you might want to give it a go..
 
Upvote 0
also, instead of copying.. once you created your formula for conditional formatting in C4 and made sure that it works for that one cell, then you go to Conditional Formatting and in the field where it shows which cells this applies to, change from C4 to C4:L22.. or whatever the columns and rows you want to include
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top