Solved

Excel Is not applying "Highlight Cells Rule" properly

Posted on 2014-09-06
4
221 Views
Last Modified: 2014-09-16
Hi Everyone,

I have an excel sheet with 3 columns. the purpose of this sheet is to show the required amount of time between Departure and Arrival, and that where the 3rd column comes to picture (Duration column).

Departure, Arrival and Duration column are set as TIME cells and formatted in 24 hour .I also applied a Highlight Cells Rule on the "DURATION column" which will highlight any cell with specific time (ex. 6min or less )with any color i select.

The issue am facing is that excel is not applying the rule on all the cells but instead some cells remain the same although they fall under the rule.

Regards,Highlight-rule.PNG
0
Comment
Question by:Khalifa_Alshamsi
  • 2
4 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40307648
Can you paste a screen clip of your rule please.
0
 
LVL 2

Accepted Solution

by:
DanielT earned 500 total points
ID: 40307682
Be sure you have no formats set manually on the cells that may confuse auto-formatting rule results.

Check your formula to ensure that references are not $A$1 for instance as is a default. You'll need to edit this to allow the reference to "move". For example - manually edit the rule and change the reference of $A$1 to $A1 to maintain same column but allow row to be referenced to new row data.

Hope this makes sense...
0
 

Author Comment

by:Khalifa_Alshamsi
ID: 40309176
@ Neilsr, find the excel attached

@DanielT, your solution worked once only. any suggestion to be able to apply it on the whole column.
0
 
LVL 2

Expert Comment

by:DanielT
ID: 40309636
The rule will apply to what your selection range is. Select the entire column - or just the range for the rule. Then ensure your rule is formatted as per last post. You should also be able to "manage" rules and change the range it applies to.

Just be careful of references so they can "move" with the rule. Excel likes to change them with it's own assumptions!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question