Conditional Formatting Question

**tryingtoexcelatexcel** · 03-12-2018, 05:33 AM

Hi All,

I tried to do conditional formatting by using a simple formula

For example
=A1=B2

If this is true make it orange

But for some reason I need to use $ for it to actually highlight

=A1=$B$2

I am attaching a sample sheet here

How come I can't get it to highlight without the $ symbol?

This make its hard to copy down as I need to go into conditional formatting to adjust the cell each time I do it.

**davsth** · 03-12-2018, 05:44 AM

I think you sheet is full of redundant conditional formats and your example above does not correspond to the cells on the sheet. What do you want to achieve?
perhaps select E5:E19
conditional format using formula then =countif($b10:$b13,e5) >0 then format as you wish

**tryingtoexcelatexcel** · 03-12-2018, 05:53 AM

Hi Davsth,

I tried your formula but nothing happened. Basically I want to highlight the dates in column E if they match the dates in column B.

**AliGW** · 03-12-2018, 05:59 AM

No, you don't. Excel will work it out for you. Your attachment doesn't match your request, however ...

**tryingtoexcelatexcel** · 03-12-2018, 06:04 AM

Hi AliGW,

Sorry I'm a bit confused. I have 4 dates in column B (Apr May Aug Sep for year 2018) and then I have a list of dates in column E.

How can I get the dates in column E to highlight if it matches the date in column B without using $.

**tryingtoexcelatexcel** · 03-12-2018, 06:09 AM

Let me try to clarify with a new file, apologies if I caused any confusion

**davsth** · 03-12-2018, 06:27 AM

for the conditional format to work for e5:e19 is has to remain a fixed range hence the $ signs. It is not reasonable to think that copying from E5 to E6 it remains fixed, but to move to rows 26 and below it doesn't and then does!

My formula works, if you delete all the formula that you currently have in the conditional formatting, I did mention redundant conditional formats earlier, which still remain.

**tryingtoexcelatexcel** · 03-12-2018, 09:09 PM

Hi Davsth,

I am attaching the sheet with your formula but for me it does not highlight.

Can you provide an example of how it works? Or does anyone else have any clue as to how it works please?

**AliGW** · 03-13-2018, 01:15 AM

Change the CF formula to this:

=MATCH(E5,$B$10:$B$13,0)>0

Do not change anything else. The applies to range is fine.

**tryingtoexcelatexcel** · 03-13-2018, 05:27 AM

Hi AliGW,

Thank you very much. I have attached the excel where I tried your version but it still does not work once I copy and paste down the format.

Please see attached. Is there a way around this without having to go in and modify the formula in conditional formatting?

Conditional Formatting Question

LinkBack

Thread Tools

Rate This Thread

Display