Bizarre Conditional Formatting results


I have an Excel sheet with three conditional formatting rules to enable easier reading of the data portrayed in the sheet but am getting unexpected results.

Rule 1:
compares a task reference in column D and adds a border to the bottom cell when the task reference changes

Rule 2:
in column A I have the following formula:


This alternates the value in column A between 1 and 0 based on the change of task reference in column D. This then formats a shade of green for the lines equal to 1.  The appearrance of the report is now green and white shading alternating with change in task reference.

Rule 3:

Checks that the Task reference is included in the summary reports on other sheets, if not it highlights red.

Rules 1 & 3 seem to work fine. However rule 2 works but some rows end up with a different shade of green.

See attached screen shot.

Any suggestions??

Rob H
LVL 37
Rob HensonFinance AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
Please post your dummy example file
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
What happens if you change the green color (say to blue). Do you have this difference in colors then?
Professor JMicrosoft Excel ExpertCommented:
1 and 3 works becuase the result of the logical test, either is true or false or 1 or 0   which triggers the conditional formatting.

however, if you look are the 2 =IF(B3="","",IF(D3=D2,A2,ABS(IF(A2="",A1,A2)-1)))  

ABS(IF(A2="",A1,A2)-1))  this part would be fine, although i do not know the values of A1 and A2

IF(D3=D2,A2   would work fine if the A2 either hold 1 or 0   (you did not specify what value is in A2)

this one IF(B3="",""  this runs first if B3 is empty then will always return false if empty . because the return value is a null string empty and your rule 2 of =$A3=1  would return false.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Rob HensonFinance AnalystAuthor Commented:
That's the bizarre thing, when I copy and paste values and formats into a copy file with a view to taking out the sensitive data, the CF works fine. I end up with alternating red and green because the red rule takes over because it can't find the task reference elsewhere but the green rule works ok, alternate lines are the same shade of green.

If you look at the screen shot I uploaded, the paler shade of green is the correct shade and is what I get for all alternate blocks on the copy file.

Rob H
Rob HensonFinance AnalystAuthor Commented:
Prof JJ - Rule 2 also gives TRUE or FALSE. The CF Formula is =$A3=1

The formula that you broke out is in cells in column A and that works fine, it alternates values 1 and 0 for alternating blocks based on the task reference in column D but allows for previous row being blank, shown on screen shot as left most column.

Philip - I tried with different colours and the same happened.

I tried removing Rule 2 and replacing but same result.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Are the green ones getting red if you remove the task reference?
Seeing the screenshot the paler green seems to be wrong - the CF shows a darker one.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I think without the sample file (which only needs to contain columns A and D), we are a bit in the dark.
Rob HensonFinance AnalystAuthor Commented:
Hi All,

Thanks for your inputs but unfortunately I was not able to upload a sample file. As soon as I replicated in a sample file it worked fine.

So I decided to remove all CF, remove all manual cell fill formats and re-apply the CF. It now works fine.

Rob H

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystAuthor Commented:
Solved by removing and replacing CF rules.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.