VBA code to find colunn number of specific data

[raven75]

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]

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]

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	4 week Period Timeline
2	Start Date:	10/27/2019			Period 3					1
3
4		week 1							week 2							week 3							week 4							Expected:
5		oct					nov		nov							nov							nov
6		sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat
7	Event:	27	28	29	30	31	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
8	Hurricane Season	Hurricane season																												Check mails / see Disaster recovery plan
Timeline Period 3

Cell Formulas
Range	Formula
K2	=MATCH(C2,C7:AD7,0)
K5	=IF(TEXT(K7,"mmm")=TEXT(J7,"mmm"),"",LOWER(TEXT(K7,"mmm")))
K6	=LOWER(TEXT(K7,"aaa"))
K7	=J7+1
C5	=LOWER(TEXT(C7,"mmm"))
C6	=LOWER(TEXT(C7,"aaa"))
C7	=C2
D5	=IF(TEXT(D7,"mmm")=TEXT(C7,"mmm"),"",LOWER(TEXT(D7,"mmm")))
D6	=LOWER(TEXT(D7,"aaa"))
D7	=C7+1
E5	=IF(TEXT(E7,"mmm")=TEXT(D7,"mmm"),"",LOWER(TEXT(E7,"mmm")))
E6	=LOWER(TEXT(E7,"aaa"))
E7	=D7+1
F5	=IF(TEXT(F7,"mmm")=TEXT(E7,"mmm"),"",LOWER(TEXT(F7,"mmm")))
F6	=LOWER(TEXT(F7,"aaa"))
F7	=E7+1
G5	=IF(TEXT(G7,"mmm")=TEXT(F7,"mmm"),"",LOWER(TEXT(G7,"mmm")))
G6	=LOWER(TEXT(G7,"aaa"))
G7	=F7+1
H5	=IF(TEXT(H7,"mmm")=TEXT(G7,"mmm"),"",LOWER(TEXT(H7,"mmm")))
H6	=LOWER(TEXT(H7,"aaa"))
H7	=G7+1
I5	=IF(TEXT(I7,"mmm")=TEXT(H7,"mmm"),"",LOWER(TEXT(I7,"mmm")))
I6	=LOWER(TEXT(I7,"aaa"))
I7	=H7+1
J5	=LOWER(TEXT(J7,"mmm"))
J6	=LOWER(TEXT(J7,"aaa"))
J7	=I7+1
L5	=IF(TEXT(L7,"mmm")=TEXT(K7,"mmm"),"",LOWER(TEXT(L7,"mmm")))
L6	=LOWER(TEXT(L7,"aaa"))
L7	=K7+1
M5	=IF(TEXT(M7,"mmm")=TEXT(L7,"mmm"),"",LOWER(TEXT(M7,"mmm")))
M6	=LOWER(TEXT(M7,"aaa"))
M7	=L7+1
N5	=IF(TEXT(N7,"mmm")=TEXT(M7,"mmm"),"",LOWER(TEXT(N7,"mmm")))
N6	=LOWER(TEXT(N7,"aaa"))
N7	=M7+1
O5	=IF(TEXT(O7,"mmm")=TEXT(N7,"mmm"),"",LOWER(TEXT(O7,"mmm")))
O6	=LOWER(TEXT(O7,"aaa"))
O7	=N7+1
P5	=IF(TEXT(P7,"mmm")=TEXT(O7,"mmm"),"",LOWER(TEXT(P7,"mmm")))
P6	=LOWER(TEXT(P7,"aaa"))
P7	=O7+1
Q5	=LOWER(TEXT(Q7,"mmm"))
Q6	=LOWER(TEXT(Q7,"aaa"))
Q7	=P7+1
R5	=IF(TEXT(R7,"mmm")=TEXT(Q7,"mmm"),"",LOWER(TEXT(R7,"mmm")))
R6	=LOWER(TEXT(R7,"aaa"))
R7	=Q7+1
S5	=IF(TEXT(S7,"mmm")=TEXT(R7,"mmm"),"",LOWER(TEXT(S7,"mmm")))
S6	=LOWER(TEXT(S7,"aaa"))
S7	=R7+1
T5	=IF(TEXT(T7,"mmm")=TEXT(S7,"mmm"),"",LOWER(TEXT(T7,"mmm")))
T6	=LOWER(TEXT(T7,"aaa"))
T7	=S7+1
U5	=IF(TEXT(U7,"mmm")=TEXT(T7,"mmm"),"",LOWER(TEXT(U7,"mmm")))
U6	=LOWER(TEXT(U7,"aaa"))
U7	=T7+1
V5	=IF(TEXT(V7,"mmm")=TEXT(U7,"mmm"),"",LOWER(TEXT(V7,"mmm")))
V6	=LOWER(TEXT(V7,"aaa"))
V7	=U7+1
W5	=IF(TEXT(W7,"mmm")=TEXT(V7,"mmm"),"",LOWER(TEXT(W7,"mmm")))
W6	=LOWER(TEXT(W7,"aaa"))
W7	=V7+1
X5	=LOWER(TEXT(X7,"mmm"))
X6	=LOWER(TEXT(X7,"aaa"))
X7	=W7+1
Y5	=IF(TEXT(Y7,"mmm")=TEXT(X7,"mmm"),"",LOWER(TEXT(Y7,"mmm")))
Y6	=LOWER(TEXT(Y7,"aaa"))
Y7	=X7+1
Z5	=IF(TEXT(Z7,"mmm")=TEXT(Y7,"mmm"),"",LOWER(TEXT(Z7,"mmm")))
Z6	=LOWER(TEXT(Z7,"aaa"))
Z7	=Y7+1
AA5	=IF(TEXT(AA7,"mmm")=TEXT(Z7,"mmm"),"",LOWER(TEXT(AA7,"mmm")))
AA6	=LOWER(TEXT(AA7,"aaa"))
AA7	=Z7+1
AB5	=IF(TEXT(AB7,"mmm")=TEXT(AA7,"mmm"),"",LOWER(TEXT(AB7,"mmm")))
AB6	=LOWER(TEXT(AB7,"aaa"))
AB7	=AA7+1
AC5	=IF(TEXT(AC7,"mmm")=TEXT(AB7,"mmm"),"",LOWER(TEXT(AC7,"mmm")))
AC6	=LOWER(TEXT(AC7,"aaa"))
AC7	=AB7+1
AD5	=IF(TEXT(AD7,"mmm")=TEXT(AC7,"mmm"),"",LOWER(TEXT(AD7,"mmm")))
AD6	=LOWER(TEXT(AD7,"aaa"))
AD7	=AC7+1

 

[Joe4]

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:

=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]

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]

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.

Dim StartDate as Date

 

[raven75]

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]

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]

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.