Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
I have 2 sheets of data, one has every day of the year and people that selected that date for vacations. I have another sheet that determines what the limitations are for each day of year.
I need to highlight the dates on sheet 1 that have more people selected than allowed - those limits are found on a different sheet. Hopefully that makes sense.
I think I have to do a nested loop but I have no idea where to start. Thanks for the help!
Re: Color range of cells (specific number of them) as determined by different cell va
Could you post an example workbook?
Re: Color range of cells (specific number of them) as determined by different cell va
Quote from Max1616;762298Could you post an example workbook?
See attached. "Calendar" is the selections made (this will eventually be much more full of data) and the allotment for each date is on "Limit_Set"... so if there are more entries on the given date than what the allotment allows, then fill the shades red for those entries over the limit and make the entries that are within limit, fill with green.
Re: Color range of cells (specific number of them) as determined by different cell va
Let me know if this works:
Sub zamael()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lCol As Integer
Dim i As Integer
Set ws1 = Sheets("Limit_Set")
Set ws2 = Sheets("Calendar")
lCol = ws2.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To lCol
If WorksheetFunction.Count(ws2.Columns(i)) - 1 > WorksheetFunction.VLookup(ws2.Cells(1, i), ws1.Range("A:C"), 3, False) Then
Cells(1, i).Interior.ColorIndex = 3
Else
Cells(1, i).Interior.ColorIndex = 4
End If
Next i
End SubSincerely,
-Max
Re: Color range of cells (specific number of them) as determined by different cell va
I must have something severely screwed up because it's coloring a row on a completely different sheet... I'll PM you my entire workbook.
Re: Color range of cells (specific number of them) as determined by different cell va
Try removing what I initially sent and add this:
Sub zamael()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lCol As Integer
Dim i As Integer
Set ws1 = Sheets("Limit_Set")
Set ws2 = Sheets("Calendar")
lCol = ws2.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To lCol
If WorksheetFunction.Count(ws2.Columns(i)) - 1 > WorksheetFunction.VLookup(ws2.Cells(1, i), ws1.Range("A:C"), 3, False) Then
ws2.Cells(1, i).Interior.ColorIndex = 3
Else
ws2.Cells(1, i).Interior.ColorIndex = 4
End If
Next i
End SubBefore I assumed the calendar was your active sheet. This should fix that.
Re: Color range of cells (specific number of them) as determined by different cell va
That works... however, not exactly what I needed. So your script changes the date heading to a different color. What I need is the grid below each date to reflect entries in and out of compliance. So get the limit from the limit_set sheet, then color all cells as pictured.
[ATTACH=CONFIG]67823[/ATTACH]
Re: Color range of cells (specific number of them) as determined by different cell va
That makes more sense! This is working for me:
Sub zamael()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lCol As Integer
Dim i As Integer
Dim lrow As Integer
Dim Days As Integer
Set ws1 = Sheets("Limit_Set")
Set ws2 = Sheets("Calendar")
lCol = ws2.Cells(1, Columns.Count).End(xlToLeft).Column
lrow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lCol
Days = WorksheetFunction.VLookup(ws2.Cells(1, i), ws1.Range("A:C"), 3, False)
ws2.Range(Cells(2, i), Cells(Days + 1, i)).Interior.ColorIndex = 4
ws2.Range(Cells(2, i), Cells(Days + 1, i)).Borders.Weight = xlThin
ws2.Range(Cells(Days + 2, i), Cells(lrow, i)).Interior.ColorIndex = 3
ws2.Range(Cells(Days + 2, i), Cells(lrow, i)).Borders.Weight = xlThin
Next i
End SubLet me know if that's what you wanted.
Sincerely,
-Max
Re: Color range of cells (specific number of them) as determined by different cell va
Works perfectly! Thank you for your help... almost done with this project.
I did have to add a worksheet select because it kept erroring out due to the macro being on the wrong sheet.
Don’t have an account yet? Register yourself now and be a part of our community!