Conditional Formatting For Previous Weeks Numbers

Question

	MOZ	Majestic	Search Console
	PA	Links	DA	Trust Flow	Citation Flow	External Backlinking	Referring Domains	Avg. Position (7d)	# of Terms	Impressions
3/14/24
	34	251	18	14	32	72	50	10.2	1,323	114,332
Sub				0	0	N/A	N/A
Root				10	28	330	98
3/21/24
	32	244	18	12	32	73	51	11.1	1,319	142,065
Sub				0	0	N/A	N/A
Root				9	27	333	99

Is there a conditional formatting formula to colour code if for example the 32 is smaller than the previous week which was 34 - the colour will be red. If it was bigger, the colour would be green? If it's the same, then there is no colour added? Same goes for the other columns? But the formula needs to be applied every time theres a new week added so it corresponds with the previous week if that makes sense?

Any help would be appreciated?

Answer 1

Hi Andreas,

I actually had one more question — for the column where it says "Avg. Position" — for that one I would need the colours to be opposite, so the higher the number than the previous week - it would need to be red and the lower the number, green. Can that be done for that column only?

Answer 2

Thank you so much Andreas! I really appreciate it.

Answer 3

Hi Kaitlin,

here is your modified sample file with the CF added.

https://www.dropbox.com/scl/fi/3eqs1m89s6an8w3vsro6v/4e63d72b-5652-4f33-aa75-6362e30d474c.xlsx?rlkey=9ps2e6k5uzhp0e0nytrd1c4tv&dl=1

It is important that you select B9 before editing the CF, otherwise the used formula will change and no longer work correctly. I applied the CF to B9:K1600 which is IMHO enough, expand the rows if needed.

Andreas.

Answer 4

Hi Andreas,

Thanks for responding.

Here's an example of my SAMPLE FILE.

For the blank one, I would be filling in the numbers and I would need it to colour code depending on whether those new numbers or higher or lower than the previous week.

Let me know if you need additional information, thank you.

Answer 5

You have to use a formula inside the conditional formatting that uses an absolute column reference but a relative row reference to compare the values.

Apply that formatting on that column. From the given data I suspect =$B1>$B6 should work.

If you need further help, we need to see your (sample) file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.