Wednesday, September 21, 2016

Solution to Error: Conditional formatting and formulas fail to apply consistently

Conditional formatting and formulas fail to apply consistently

I have a sheet with several cells with (more or less) identical conditional formats.

Each of the cells has two different rules.

J10, for instance, has 1 rule that uses a formula comprised of a VBA script to check a value in E13 and determine if it is in a range defined in E4.
If that formula returns FALSE, J10 changes to red text and pink background.

Above that rule in the Rules Manager is a second rule that uses a simple ISNUMBER function to see if E13 is a number.
On a FALSE condition, it sets the cell background to white.

So, if E13 is not a number, the cell is white, and if it is a number, but not in the specified range, it turns red.

So far, so good.

I have 26 cells in various parts of the sheet set up this way.
Some of them work, some of them don't.

During a change in window focus (for instance switching from Excel to IE, or vice versa) the cells format correctly, but then loose their formatting and go back to what they were before the focus shift within a few seconds.

On the same sheet I have a column of numbers (the same ones in column E that I use to check the conditions) that does not update displayed formula results.

For instance, if E12 is "1" and I type "=E12+1" as the formula for E13, it evaluates to 2.
If I subsequently change E12 to "150", E13 still displays "2", but a calculation in J10 updates and evaluates as though E13 is the correct value of 151.

Workbook Calculation is set to Automatic.
If I set it to Manual and check Calculate before saving, and close and save the workbook, when it opens again, all of the values and formats are correct, but any change causes any of the above problems, seemingly
randomly.

Other workbooks do not have this problem.


EDIT:

I finished making some changes to a (seemingly) unrelated part of the workbook- a macro that reads the values in the cells to compile a string which is sent out through email- and now the first six cells with the conditional formatting (I10, J10, I15,
J15, K15, and L15) all work correctly each time.
The other 20 cells all make the change to red when "bad data" is entered, but then immediately (within a second) go back to no formatting (but they do this consistently.)


SECOND EDIT:

When I close the file, I get a dialog box with "Calculation is incomplete.
Recalculate before saving?"

This occurs unless I set the Calculation option to Manual without recalculate on close.

When I open the file with calculation set to manual, any given conditional format is correct for the data in the associated field.
Any attempt to do a recalculate goes back to the issue of the first six cells working, but the other 20 not working.

Keys to the Problem Conditional formatting and formulas fail to apply consistently

Download Error Fixer for Free Now

The problem seems to have been the old "too many conditionals."

I fixed the issue by creating formulas to determine a true/false condition that reported to a cell beneath the ones I was formatting, which allowed me to eliminate one condition for each cell and at the same time use a relative reference for multiple cells,
which brought me from 52 conditional statements in the Conditional Format Rules Manager to only two statements total.

This also fixed the display issue with the "simple" calculations in unconnected cells.

Use the System File Checker tool to repair corrupted or missing system files:

  1. Click Start.
  2. Type cmd, and then press CTRL+SHIFT+ENTER to open the elevated command prompt (I.e. Command prompt with administrative rights).
  3. The User Account Control window should appear, be sure that the action it exhibits is correct, and then press the Continue button.
  4. Type or copy paste the below command line into it without quotes:
  5. "Sfc /scannow"

Recommended Method to Fix the Problem: Conditional formatting and formulas fail to apply consistently:

How to Fix Conditional formatting and formulas fail to apply consistently with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your system.  Open it, and it will perform a scan for your system. The errors will be shown in the list.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been fixed.


Related: Best Way to Download Satellite L645-SP4004M WinDVD BD for Toshiba v.10.0.6.110 driver,Download Toshiba Satellite C655D-S5057 Wireless LAN Driver v.8.0.0.258.0,Method to Update & Download Toshiba Satellite L650-BT2N22 Web Camera Application v.2.0.3.37 driver,How Can You Update & Download Toshiba Satellite L775D-S7220GR Value Added Package v.1.6.0130.640204 driver,Download Toshiba Satellite Pro L750-SP5176FM Sleep Utility v.1.4.0025.000101 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.8.17.12.6686,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.11.6.0.1030,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.9.3.0.1019,Method to Update & Download ASUS K41VD Intel INF Update Driver v.9.1.1.1015,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver
Read More: Concerns about refreshing PC Tech Support,[Solution] Computer will shutdown\/restart on its own!,How to Fix - computer keeps crashing when I go to event viewer?,How Can You Fix - computer won't come out of hiberntion?,How to Fix Problem - Computer won't shut down...'task scheduler engine process is executing shutdown shutdown processes?,Can't change homegroup sharing permission,Can't connect XP shared printer to Win7 except as a local printer.__,Can You Connect the LAN Adapter for Wii On your Windows 7 Laptop?,Can These Files Be Deleted,Can’t install Microsoft Flight Simulator X Gold Edition error 1722

No comments:

Post a Comment