VBA code to find colunn number of specific data

raven75

New Member
Joined
Nov 5, 2019
Messages
4
Im using this code to color an specific row, from the starting column to the end column for a 4 weeks timeline graphic (not a grant chart) but since I'm using dates as headers the columns are not corresponding with the dates

can you please help me to find the column number (StartColumn) based on the date I need?
e.g.
11/05/2019 = column 5

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub coloring(EvenName As String, StartColumn As Integer, EndColumn As Integer, Row As Integer, EventColor As Long)
Static X As Integer
Cells(Row, StartColumn).Value = EentName
For X = StartColumn To EndColumn
Cells(Row, X).Interior.Color = EventColor
Next[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]End Sub[/FONT]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,165
Office Version
365
Platform
Windows
Welcome to the Board!

I think your question would be much clearer if we could see what your data looks likes, and see exactly what you are trying to do.

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html. Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.
 

raven75

New Member
Joined
Nov 5, 2019
Messages
4
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;color: #E26B0A;;">4 week Period Timeline</td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td><td style="font-weight: bold;color: #E26B0A;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #808080 ;;"> Start Date:</td><td style="text-align: center;border-bottom: 1px solid black;color: #808080 ;;">10/27/2019</td><td style="text-align: center;border-bottom: 1px solid black;color: #808080 ;;"></td><td style="text-align: center;border-bottom: 1px solid black;color: #808080 ;;"></td><td style="color: #808080 ;;">Period 3</td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;">1</td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="color: #404040;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;color: #404040;;"></td><td style="text-align: center;border-top: 1px solid black;color: #404040;;"></td><td style="text-align: center;border-top: 1px solid black;color: #404040;;"></td><td style="text-align: center;border-top: 1px solid black;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: center;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="text-align: right;color: #404040;;"></td><td style="color: #404040;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;color: #404040;background-color: #F2F2F2;;"></td><td style="background-color: #D9D9D9;;">week 1</td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #BFBFBF;;">week 2</td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #D9D9D9;;">week 3</td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #BFBFBF;;">week 4</td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="color: #404040;background-color: #F2F2F2;;">Expected:</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;color: #404040;background-color: #F2F2F2;;"></td><td style="background-color: #D9D9D9;;">oct</td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;">nov</td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #BFBFBF;;">nov</td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #D9D9D9;;">nov</td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #D9D9D9;;"></td><td style="background-color: #BFBFBF;;">nov</td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="background-color: #BFBFBF;;"></td><td style="color: #404040;background-color: #F2F2F2;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;color: #404040;background-color: #F2F2F2;;"></td><td style="background-color: #D9D9D9;;">sun</td><td style="background-color: #D9D9D9;;">mon</td><td style="background-color: #D9D9D9;;">tue</td><td style="background-color: #D9D9D9;;">wed</td><td style="background-color: #D9D9D9;;">thu</td><td style="background-color: #D9D9D9;;">fri</td><td style="background-color: #D9D9D9;;">sat</td><td style="background-color: #BFBFBF;;">sun</td><td style="background-color: #BFBFBF;;">mon</td><td style="background-color: #BFBFBF;;">tue</td><td style="background-color: #BFBFBF;;">wed</td><td style="background-color: #BFBFBF;;">thu</td><td style="background-color: #BFBFBF;;">fri</td><td style="background-color: #BFBFBF;;">sat</td><td style="background-color: #D9D9D9;;">sun</td><td style="background-color: #D9D9D9;;">mon</td><td style="background-color: #D9D9D9;;">tue</td><td style="background-color: #D9D9D9;;">wed</td><td style="background-color: #D9D9D9;;">thu</td><td style="background-color: #D9D9D9;;">fri</td><td style="background-color: #D9D9D9;;">sat</td><td style="background-color: #BFBFBF;;">sun</td><td style="background-color: #BFBFBF;;">mon</td><td style="background-color: #BFBFBF;;">tue</td><td style="background-color: #BFBFBF;;">wed</td><td style="background-color: #BFBFBF;;">thu</td><td style="background-color: #BFBFBF;;">fri</td><td style="background-color: #BFBFBF;;">sat</td><td style="color: #404040;background-color: #F2F2F2;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;border-bottom: 1px solid black;color: #404040;background-color: #F2F2F2;;">Event:</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">27</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">28</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">29</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">30</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">31</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">1</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">2</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">3</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">4</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">5</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">6</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">7</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">8</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">9</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">10</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">11</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">12</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">13</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">14</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">15</td><td style="border-bottom: 1px solid black;background-color: #D9D9D9;;">16</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">17</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">18</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">19</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">20</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">21</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">22</td><td style="border-bottom: 1px solid black;background-color: #BFBFBF;;">23</td><td style="border-bottom: 1px solid black;color: #404040;background-color: #F2F2F2;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #F2F2F2;;">Hurricane Season</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;">Hurricane season</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #D8E4BC;;"></td><td style="border-top: 1px solid black;border-bottom: 1px solid black;color: #404040;background-color: #F2F2F2;;">Check mails / see Disaster recovery plan</td></tr></tbody></table><p style="width:13.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Timeline Period 3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=MATCH(<font color="Blue">C2,C7:AD7,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C5</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">C7,"mmm"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">D7,"mmm"</font>)=TEXT(<font color="Red">C7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">D7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">E7,"mmm"</font>)=TEXT(<font color="Red">D7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">E7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">F7,"mmm"</font>)=TEXT(<font color="Red">E7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">F7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">G7,"mmm"</font>)=TEXT(<font color="Red">F7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">G7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">H7,"mmm"</font>)=TEXT(<font color="Red">G7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">H7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">I7,"mmm"</font>)=TEXT(<font color="Red">H7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">I7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J5</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">J7,"mmm"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">K7,"mmm"</font>)=TEXT(<font color="Red">J7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">K7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">L7,"mmm"</font>)=TEXT(<font color="Red">K7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">L7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">M7,"mmm"</font>)=TEXT(<font color="Red">L7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">M7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">N7,"mmm"</font>)=TEXT(<font color="Red">M7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">N7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">O7,"mmm"</font>)=TEXT(<font color="Red">N7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">O7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">P7,"mmm"</font>)=TEXT(<font color="Red">O7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">P7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q5</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">Q7,"mmm"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">R5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">R7,"mmm"</font>)=TEXT(<font color="Red">Q7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">R7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">S5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">S7,"mmm"</font>)=TEXT(<font color="Red">R7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">S7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">T7,"mmm"</font>)=TEXT(<font color="Red">S7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">T7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">U5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">U7,"mmm"</font>)=TEXT(<font color="Red">T7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">U7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">V5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">V7,"mmm"</font>)=TEXT(<font color="Red">U7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">V7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">W5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">W7,"mmm"</font>)=TEXT(<font color="Red">V7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">W7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">X5</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">X7,"mmm"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Y5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">Y7,"mmm"</font>)=TEXT(<font color="Red">X7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">Y7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Z5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">Z7,"mmm"</font>)=TEXT(<font color="Red">Y7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">Z7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AA5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">AA7,"mmm"</font>)=TEXT(<font color="Red">Z7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">AA7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AB5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">AB7,"mmm"</font>)=TEXT(<font color="Red">AA7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">AB7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AC5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">AC7,"mmm"</font>)=TEXT(<font color="Red">AB7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">AC7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AD5</th><td style="text-align:left">=IF(<font color="Blue">TEXT(<font color="Red">AD7,"mmm"</font>)=TEXT(<font color="Red">AC7,"mmm"</font>),"",LOWER(<font color="Red">TEXT(<font color="Green">AD7,"mmm"</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">C7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">D7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">E7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">F7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">G7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">H7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">I7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">J7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">K7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">L7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">M7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">N7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">O7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">P7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">Q7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">R6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">R7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">S6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">S7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">T7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">U6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">U7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">V6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">V7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">W6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">W7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">X6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">X7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Y6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">Y7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Z6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">Z7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AA6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">AA7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AB6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">AB7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AC6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">AC7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AD6</th><td style="text-align:left">=LOWER(<font color="Blue">TEXT(<font color="Red">AD7,"aaa"</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C7</th><td style="text-align:left">=C2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D7</th><td style="text-align:left">=C7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E7</th><td style="text-align:left">=D7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F7</th><td style="text-align:left">=E7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G7</th><td style="text-align:left">=F7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H7</th><td style="text-align:left">=G7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I7</th><td style="text-align:left">=H7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J7</th><td style="text-align:left">=I7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K7</th><td style="text-align:left">=J7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L7</th><td style="text-align:left">=K7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M7</th><td style="text-align:left">=L7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N7</th><td style="text-align:left">=M7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O7</th><td style="text-align:left">=N7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">P7</th><td style="text-align:left">=O7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Q7</th><td style="text-align:left">=P7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">R7</th><td style="text-align:left">=Q7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">S7</th><td style="text-align:left">=R7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T7</th><td style="text-align:left">=S7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">U7</th><td style="text-align:left">=T7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">V7</th><td style="text-align:left">=U7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">W7</th><td style="text-align:left">=V7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">X7</th><td style="text-align:left">=W7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Y7</th><td style="text-align:left">=X7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Z7</th><td style="text-align:left">=Y7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AA7</th><td style="text-align:left">=Z7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AB7</th><td style="text-align:left">=AA7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AC7</th><td style="text-align:left">=AB7+1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">AD7</th><td style="text-align:left">=AC7+1</td></tr></tbody></table></td></tr></table><br />
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,165
Office Version
365
Platform
Windows
I think you need to build the actual date, and then you can work off of that.
You could always do it in row 3, and change the font color to white if you want it "hidden".

If you are using American-style dates, you could place this formula in cell C3:
Code:
=DATEVALUE(C5 &" "&C7&", "&YEAR(TODAY()))
Then, you could put the following in D3 and copy across:
=C3+1

Just note that things could get a little tricky as you cross over years (since you don't seem to be entering/saving the year portion of the date anywhere).
 

raven75

New Member
Joined
Nov 5, 2019
Messages
4
i was trying this:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]StartDate = InputBox("Enter stating date", "Start Date")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]StartColumn = WorksheetFunction.Match(StartDate, Range("C7:D7"), 0)

but it shows this error message:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]unable to get the match property of the worksheetfunction class[/FONT]<strike></strike>
[/FONT]
<strike></strike>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,165
Office Version
365
Platform
Windows
But you don't actually have a date in cell C7, you just have a number (27).
You want to make sure you are comparing "apples-to-apples" here.

Note that you should also first declare the variable type in your code to make sure it is taking the input as a date and not text or a number, i.e.
Code:
Dim StartDate as Date
 

raven75

New Member
Joined
Nov 5, 2019
Messages
4
here is my code
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Column()
Dim StartDate, PeriodStartDate As Date, StartColumn As Integer, Data As Integer[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]PeriodStartDate = Range("c2").Value
StartDate = InputBox("Enter stating date", "Start Date")
StartColumn = WorksheetFunction.Match(StartDate, Range("C7:D7"), 0)[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
End Sub
and on the sheet the range from c7 to d7 have formulas to sum 1 day each cell from the starting date dates but the format is to show only the day
[/FONT]
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,165
Office Version
365
Platform
Windows
Dim StartDate, PeriodStartDate As Date, StartColumn As Integer, Data As Integer
Note that this does NOT declare "StartDate" as Date, but rather as Variant (since you do not have " As Date" after it; each variable needs to be declared explicitly).
So that will allow StartDate to be Text entries, which you do not want if you are comparing to dates.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,476
Office Version
365
Platform
Windows
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1295451-help-with-function.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Forum statistics

Threads
1,078,345
Messages
5,339,672
Members
399,318
Latest member
kryten68

Some videos you may like

This Week's Hot Topics

Top