Conditional formatting with OR and INDIRECT functions not working

Some extra information (For context see attachment):
Instead of the formula below (which I used in the attached workbook) I have also tried a few other ways.
=OR(INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+2;COLUMN(B2);1;1)&":"&ADRESS(FLOOR((ROW(B2)-2)/4)*4+5;COLUMN(B2);1;1))=$H$12)

Tried alternatives:
1. =OR(INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+2;COLUMN(B2);1;1))=$H$12;=OR(INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+3;COLUMN(B2);1;1))=$H$12;=OR(INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+4;COLUMN(B2);1;1))=$H$12)
2. =IF(INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+2;COLUMN(B2);1;1))=$H$12;-1;1)*IF(INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+3;COLUMN(B2);1;1))=$H$12;-1;-1)*IF(INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+4;COLUMN(B2);1;1))=$H$12;-1;1)=-1

Perhaps these formulas give you a better idea of what it is I'm trying to achieve.

Sincerly,

Noah Disseldorp

Thank you for your quick reply Ali,

If I have understood you, you cannot take the formatting of one cell and apply it to another using a formula, so referencing H12 won't work. You need to set the formatting in the rule itself.

- In that case why does the formula work when I remove the OR function. If I am not mistaken, it is this part of the formula -- ADRESS(FLOOR((ROW(B2)-2)/4)*4+2;COLUMN(B2);1;1) -- which determines which cell to check the value is equal to $H$12. The way I wrote it the adress function returns B2 for B2, B3, B4 and B5 and it returns B6 for B6, B7, B8 and B9. The idea being that it checks the same cell for a group of 4 cells and then moves on to the next group of 4 cells. Now If I want to return B3 for B2, B3, B4 and B5 I change ADRESS functions the row definition from -- FLOOR((ROW(B2)-2)/4)*4+2 -- to -- FLOOR((ROW(B2)-2)/4)*4+3
So in short, each cell in the range I want the conditional formatting to apply is either TRUE or FALSE, and - when not using the OR function - it works perfectly, like I expect it to. But then, once I do use the OR function it no longer works, even though the exact same cells are being referenced as in the function without OR. Even more surprisingly the function with OR does work if I enter it in a cell below the range B2:F13 and spread it out over a range of the same size. The reference of $H$12 not being used not to take its formatting but to determine wether the function is TRUE of FALSE or in other words whether formatting for the cell should be applied.

It would help if you explained in WORDS what the objective is rather expecting us to reverse engineer formulae that are not actually doing what you want. Some detailed annotations in the workbook would help.

- On to the (in my opinion) more difficult question. As stated in the workbook, I have a table with some base data. I want to automatically retrieve the data in a planner-like view, this is being done by giving dates and times in columns next to the discipline, relation, location and description. Each row represents a task or a workflow and the information in the colums is either data to help with automatically placing the task in the planner-view or the data I want to place in the planner-view (the previously mentioned columns).
Each task stretches over a multiple of cells like bars from top to bottom, I want to apply formatting to the 4 top cells of the "task card". Below an example of one of these task cards:

2023-10-13_135157.png

All the functionality of getting the proper data in the right place is working, and I am now down to applying the conditional formatting. I would like to use the formula's mentioned in my previous reply and used in the attached excel workbook to apply the formatting of my choice to tasks wich have either the discipline, relation or location in common.
Usually I'd simply use Microsoft Project, but for reasons which would even take longer to explain I was hoping I could do it in excel. If all else fails I can always create more rules for the conditional formatting and basically force out the OR rule. However that is not really future-proof, if ever, I would want to be able to sort / colorcode on new columns containing new data I'd like to add to the "flashcards".


Sorry for the extremely lengthy response, I understand that everyone is helping me in their free time, nevertheless, I believe it is a challenging and fun problem to solve and I wanted to share it. If it turns out it is simply not possible to do what I want to do, then so be it.
Thank you all for your time.


Sincerely,

Noah Disseldorp

Dear Ali,

Thank you for your quick reply again. Indeed it takes me a lot of effort to explain, to be frank, I'm having a hard time doing so. However I'll give it another go using the picture below.
2023-10-13_142528.png

Don't worry creating the picture wasn't a lot of effort ;-)

I still believe my previous response gives anyone the best chance of understanding what the problem is I've encountered. To put it simply:
I have a working formula with the INDIRECT(ADRESS()) combination which works both in cells in the workbook and in the conditional formatting, this formula has three variations which all work seperately in the way I want them to work.
However, when I combine the three formulas (which work) using an OR function, like this: OR(Variation1=Value; Variation2=Value; Variation3=Value). It still works in a cell in the workbook, but stops working in the conditional formatting rule.
Variation1: =INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+2;COLUMN(B2);1;1))=$H$12 | works individually | returns TRUE
Variation2: =INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+3;COLUMN(B2);1;1)) | works individually | returns FALSE
Variation3: =INDIRECT(ADRESS(FLOOR((ROW(B2)-2)/4)*4+4;COLUMN(B2);1;1)) | works individually | returns FALSE
Combination: =OR(Variation1=Value; Variation2=Value; Variation3=Value) | works only in a cell in the workbook | returns FALSE

I don't think I can explain the problem any better the in the paragraph above. As for the purpose, I am hoping the attached picture will suffice.
(I am not saying there isn't a better way to explain it or to make myself clear, but I don't think I can come up with one.)

Again, thank you for your time!

Sincerely,

Noah Disseldorp