Conditional Formatting - Copy across columns/Rows

Morning - I can never seem to follow or understand how you create a conditional format in a column or row and then create/apply the same formatting across other columns/rows without having to type out a formula every time! Just won't go in my head - so if someone can explain simply please? It's the 'range' and 'absolutes' which throw me every time

In the attached I have a list of people and I need to put them into a rota

Rows 3 to 25 are the personnel (and when they start their shift)
Rows 27 to 33 are the places they need to be at a certain point in the day

I would like the names in rows 3 to 25 to change to green when they are given a time slot in 27 to 33.

I've tried countif(D27:D33,D3)>0 and I believe it's right, but I cannot for the life of me work out how to copy it to columns E through W and then for the same formatting to work in rows 36 to 58 (applying to rows 60 to 66) and so on down the page so that each day is covered

I then want for a row to highlight yellow when the word "Absent" appears in X

Thanks for any help you can give - just can't seem to follow this or the advice seen online elsewhere! Excel F - Cond Formatting.xlsx

select D3:W25

then use the formula as you have
countif(D27:D33,D3)>0
BUT you need to use $

so you want the rows 27 to 33 - to always be checked , but appy across columns d to w - so the column needs to move
countif(D$27:D$33,D3)
you dont really need the >0 test as 1 or more is seen as true by conditional formatting

this may help

imagine conditional formatting as a loop test

so it starts where you first select D3 and stops at the end of the selection W25

in D3 it starts the test
countif(D$27:D$33,D3) - so checks d27 to d33 to see if that range has the same contents as D3
now it moves down to D4 , BUT because of the $ - it does not then check D28 to D34 - it has to check D27 to D33 again
and so on down the rows upto D25 (as thats the row at the end of the selection

NOW it moves to column E
and because there are NO $ on the column the formula is effectively

countif(E$27:E$33,E3)
as no $
and repeats the column again - then moves onto next

I dont know how to copy that formatting to the next section - as the $ will be wrong

so i would would simply repeat the conditional formatting my self and enter it effectly for each block

the absent is different - because you are only testign for the word in column X and it wont be entered in 27 to 33
so for that you could select the entre range

D3 to W157

the formula to use is

=$X3 = "absent"

again image the loop

when in D3 it checks X3 for the word
when in E3 it checks X3 for the word - because the $ fixes the column x
BUT you only want to highlight if there is a name in the cell - ie NOT blank
so is D3 blank and is X = absent
so the formula now is
=AND(D3<>"",$X3="absent")
so loop again
=AND(E3<>"",$X3="absent")
is E3 blank - yes in this case - so FALSE - so conditional formatting will not be applied

Formatting in rows 27 to 34 - I dont follow the example - they will all be highlighted as they all exist in the rows above in your example

i have removed from D21 the name shova and you cna see its not highlighted in rows 27 to 34 now

i hope that helps - if not i will be happy to try and explain again

i have done the yellow formatting for all th espreadhseet and just the 1st DAY - block so the conditional formatting needs to be applied for the other days

There maybe a way to copy this - i dont know it - other members may know - or provide a macro to do it

glad to have helped
as i say , there maybe a way to copy the formatting to the different ranges - i just dont know

as you re-read please just ask if you have more questions