Since the data is not properly `structured`

as it should be hence, you have two options, either to `restructure`

the data or else use `two helper columns`

in `Sheet2`

so that the `Conditional Formatting`

can be applied in the required areas of `Sheet1`

.

I am bit late as **FlexYourData** Sir has given you the right way on restructuring the data and using `Conditional Formatting`

, hence I'm opting out for the second alternative i.e. using `two helper columns`

in `Sheet2`

, which is also possible to do.

As I am not sure about your `Excel Version`

and assuming there is no `Excel`

Constraints as per the tags posted, then you could try the following.

As it can be seen two helper columns has been added on `Sheet2`

far right, and applied the following formulas:

• Formula used in cell `J3`

```
=SCAN(0,B3:B9,LAMBDA(x,y,IF(ISTEXT(y),x,y)))
```

&,

• Formula used in cell `K3`

```
=SCAN(0,B3:B9,LAMBDA(x,y,IF(ISNUMBER(y),"",y)))
```

In `Sheet1`

select the range `C4:I8` hit `ALT`+`H`+`L`+`N` which opens the `New Formatting Rule`, Select the last rule --> `Use A Formula To Determine Which Cells To Format` and enter the following formula in the rule description.

```
=XMATCH($B4&"|"&C$2,Sheet2!$K$3#&"|"&Sheet2!$J$3#)
```

Select preferred formatting fill color from the tabs and press `OK`

twice.

If you are not using `MS365`

then you need to use the following formulas in place of those above in `Sheet2`

• Formula used in cell `J3`

```
=IF(ISTEXT(B3),J2,B3)
```

&,

• Formula used in cell `K3`

```
=IF(ISNUMBER(B3),"",B3)
```

Bothe above formulas needs to be filled down!.

In `Sheet1`

in `Conditional Formatting`

Rule.

```
=MATCH($B4&"|"&C$2,Sheet2!$K$3:$K$9&"|"&Sheet2!$J$3:$J$9,0)
```

All the steps for using `Conditional Formatting`

remains same except the formulas are different. Please note to change the cell ranges and references as per your suit. Also, it is highly suggested to follow proper data restructuring.

In `Google-Sheets`

the same formulas worked without needing to have an `additional helper columns`

:

The formulas needs to be placed in `Sheet1`

and needs to be filled down and filled right:

```
=IFERROR(ARRAYFORMULA(XMATCH($B4&"|"&TEXT(C$2,"mmddyyyy"),
SCAN(0,Sheet2!$B$3:$B$9,LAMBDA(x,y,if(ISNUMBER(y),"",y)))&"|"&
TEXT(SCAN(0,Sheet2!$B$3:$B$9,LAMBDA(x,y,if(ISTEXT(y),x,y))),"mmddyyyy"))),"")
```

- To set the
`Conditional Formatting`

Select the range `C4:I8`

- Goto
`Format`

Tab & select `Conditional Formatting`

- On selecting it opens a panel on the right hand side of the sheets, under
`Format Rules`

select `Greater Than`

and enter `0`

next.
- In the
`Formatting Style`

choose both `Fill`

& `Text`

Color as same and press `Done`

. That should work on your end.
- Additionally, since the sheet is formulated, you can make the sheet
`Protected`

so that no one make any changes to the formulas to mess up the algorithms.