Conditional Formatting between sheets.

[Want2BExcel]

Hi,

I could use some help. I made this from scratch, following a tutorial on how to make a yearly planning calendar and applying my own changes, so it is a monthly planner that is changeable by dropdowns in months and years. When I change the year, all I have to do in apply what weekday the first day is, which also is a dropdown list. So it’s all dynamic and works great

I have 2 problems:

I use this formula in conditional format, applied to range $F$7:$AP$7 to color date cell if there’s an entry on a date. This works perfectly. The 5 days in January 1,2,12,13,14 are colored yellow

=AND(F7<>"",ISNUMBER(F7)=TRUE,COUNTA(INDIRECT("'"&QtNm&"'!R8C" &F7-DATE(ScYear,1,1)+1&":R81C" &F7-DATE(ScYear,1,1)+1,FALSE))>0)

Whether to color or not, is determined by looking for data in "'"&QtNm&"' which is a variable sheet, depending on what month the data is located. If January-Marts it’s Qt.1 (displayed name: 1.kvartal) and April-June it’s Qt.2 (displayed name: 2.kvartal) etc. These sheets are datasheets. The data is entered with VBA, so when a date is selected (range $F$7:$AP$7) in the main sheet (Planner), AR7 is showing the selected date and if data is typed in AR8:AR72 it’s stored in the datasheet in a cell. Each column in the datasheet represents a day (1/1 is A and 2/1 is B etc. BUT it’s continuous, so even though April 1st is a new datasheet it doesn’t starts in column A but in CM. See problem 2 for explanation). The cell is determined by which row the data is entered. So if data is entered 13/1 in AR8 it will be stored in row 8, column M

PROBLEM 1: (Important)

I really don’t need the above conditional formatting, coloring the date. I just made it because I hoped it would make it easier to edit it to my idea…guess not! What I really want is not the date colored, but the empty cells below (See picture, where Q9 is colored manually). Which cell should be determined by which row data is entered in range AR8:AR72, keeping in mind that it’s not actually this range where the data is. This range just display the data. The data is in the datasheets (See picture) which the formula should use or else the color would disappear whenever I change the day, month or year.

PROBLEM 2: (Optional)

Since the tutorial was a yearly planner, the developer intended each datasheet to contain data for a full year (aprox. 365 columns). This means, that even though I have changed it to work quarterly, 3 months per sheet, which is working. I can’t see how to change the VBA code to “reset” entering data in column A, when changing from datasheet to datasheet. As the code is now Qt.1 ends with row CL as it should, but then Qt.2 is beginning from row CM which is a little annoying. But!!! Before changing this, I’m wondering if it could cause problems elsewhere, like ribbels in a lake. Eg. With the conditional format formula above, where it’s using the column number to determine which day it is. Changing this may cause this to color multiple dates if both January 1st, April 1st, July 1st and October 1st is column A. So somehow I have to make excel aware that when data is entered on April 1st, the datasheet Qt.2 is now in column A and not CM, and so forward with Qt.3 and Qt.4. Is there an “easy” fix to this or is it simply to much work to satisfy my OCD, LOL?

I hope someone can assist me…thank you! I would have pasted it as a mini sheet, but everytime I try that button, the "wheel" just spinning and nothing happens. I even tried letting it "spin" the night through, but it don't work. Not even with one single cell selected Excel is not responding.

	2022			1.kvartal								JANUAR
	Årets første dag:
	Lørdag
					Lø	Sø	Ma	Ti	On	To	Fr	Lø	Sø	Ma	Ti	On	To	Fr	Lø	Sø	Ma	Ti	On	To	Fr	Lø	Sø	Ma	Ti	On	To	Fr	Lø	Sø	Ma	Ti	On	To	Fr	Lø	Sø		DAGLIGE AKTIVITETER
	AKOS				1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31								onsdag, 12 januar 2022
																																											Vacation

Dim YearNm As Long, DayCol As Long, DayRow As Long
Dim DataSheet As Worksheet
Dim SelDate As Date

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AR8:AR72")) Is Nothing Then
    SelDate = Range("AR7").Value 'Selected date
    YearNm = [ScYear]
    'Determine if data worksheet exists
    On Error Resume Next
    QuaterNm = [QtNm]
    Set DataSheet = ThisWorkbook.Sheets("" & QuaterNm & "")
    On Error GoTo 0
    If DataSheet Is Nothing Then
        ThisWorkbook.Sheets.Add(Before:=Sheets("Planner")).Name = QuaterNm
        Set DataSheet = ThisWorkbook.Sheets("" & QuaterNm & "")
        Activate
    End If
    DayRow = Target.Row 'Row
    DayCol = SelDate - DateSerial(YearNm, 1, 1) + 1 'Determine column for data sheet
    DataSheet.Cells(DayRow, DayCol).Value = Target.Value
End If
   
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("F7:AP7")) Is Nothing Then
    If IsDate(Target.Value) = False Then Exit Sub 'If a date is NOT selected then no data will appear in "Daglige Aktiviteter"
    SelDate = Target.Value 'Selected date
    YearNm = [ScYear]
    Range("AR7").Value = SelDate
    'Determine if data worksheet exists
    On Error Resume Next
    QuaterNm = [QtNm]
    Set DataSheet = ThisWorkbook.Sheets("" & QuaterNm & "")
    On Error GoTo 0
    If DataSheet Is Nothing Then
        ThisWorkbook.Sheets.Add(Before:=Sheets("Planner")).Name = QuaterNm
        Set DataSheet = ThisWorkbook.Sheets("" & QuaterNm & "")
        Activate
    End If
    DayRow = Target.Row 'Row
    DayCol = SelDate - DateSerial(YearNm, 1, 1) + 1 'Determine column for data sheet
    Range("AR8:AR72").Value = DataSheet.Range(DataSheet.Cells(8, DayCol), DataSheet.Cells(72, DayCol)).Value
End If
End Sub

Showing what I want. Coloring Q9
For info, if not showing in the table above. The year cell is equal til ScYear and "1.kvartal" is equal to QtNm
(The year and month can be changed by dropdown, resulting in dates moving back and forth between F and AP)

The datasheet Qt.1 [1.kvartal], showing where the above data entry (AR9=Vacation) is stored

 

[Want2BExcel]

PROBLEM 1 SOLVED!

Solution:

=AND(F$7<>"",ISNUMBER(F$7)=TRUE,COUNTA(INDIRECT("'"&QtNm&"'!R[0]C"&F$7-DATE(ScYear,1,1)+1&":R[0]C"&F$7-DATE(ScYear,1,1)+1;FALSE))>0)

It was pretty “simple” only a little R[0] instead of R8

So now there's only the "optional" problem left. Could that be fixed just as easy or is it just as complicated as I'm afraid of

PROBLEM 2: (Optional)

Since the tutorial was a yearly planner, the developer intended each datasheet to contain data for a full year (aprox. 365 columns). This means, that even though I have changed it to work quarterly, 3 months per sheet, which is working. I can’t see how to change the VBA code to “reset” entering data in column A, when changing from datasheet to datasheet. As the code is now Qt.1 ends with row CL as it should, but then Qt.2 is beginning from row CM etc. which is a little annoying.

But!!! Before changing this, I’m wondering if it could cause problems elsewhere, like ribbels in a lake. Anybody care to comment on this?
Eg. With the conditional format formula above, where it’s using the column number to determine which day it is. Changing this may cause this to color multiple dates if both January 1st, April 1st, July 1st and October 1st is column A. So somehow I'm not only have to rewrite some VBA, to make excel aware that when data is entered on April 1st, the datasheet Qt.2 is now in column A and not CM, and so forward with Qt.3 and Qt.4. I properly also have to change something in my conditional formatting.
There is no “easy” fix to this, is there? It is properly to much work to satisfy my OCD Feel free to comment on this!!!

 

[Want2BExcel]

=AND(ISNUMBER(F$7),COUNTA(INDIRECT("'"&QtNm&"'!R[0]C"&F$7-DATE(ScYear,1,1)+1&":R[0]C"&F$7-DATE(ScYear,1,1)+1;FALSE))>0)

Still works after a little clean up