Wednesday, September 21, 2016

Solution to Problem: Conditional Formatting Cells Which Contain ANY Text

Conditional Formatting Cells Which Contain ANY Text

Dear All,
 
I am doing a mail merge.
Still.
Data is drawn from various databases and most information is dropped into an Excel table using VLOOKUP function.
As with most such exercises, there are blanks, and duff information.
I have CF'd the blanks.
That was not beyond my ken.
Next, we are going to fill in all of those blanks with 'proper' data.
This will over-write the cells from a formula to text.
I would therefore like to CF those cells so as to give a visual cue and track those over-written cells.
 
There is a lot of guidance on the 'net about tracking formulae in this way - such as using get.cell and attribute 48.
I have tried replacing 48 with 46 as this according to MrExcel's long list of arguments = TRUE if the cell contains a text note.
As this
has failed abysmally, I conclude a text note =^F2-type note.
Or I cannot type.
It works on formulae with 48, so I've not lost the thread completely.
I have also tried using Allen Wyatt's Excel Ribbon Tips Function rCell.HasFormula and replacing it with "HasText" but this didn't work.
I don't think there is a "HasText".
But my HasFormula macro didn't work as originally, even with formulae.
(I did it in
VBA Project (ThisWkBk) Sheet1.
Is this why? I can never decide if it has to go into a module).
UPDATE - HasFormula original works in a module, so module it is.
 
Anyway, tried and failed.
ISTEXT() isn't working.
Or rather, it's working too well, and is returning True for almost all fields including those with =VLOOKUP() functions - presumably because the value returned by the LOOKUP function is also text.
So I want something that's smart
enough to spot only cells where the default formula has been overwritten and replaced with "dumb" text.
Many thanks,
M

Anwsers to the Problem Conditional Formatting Cells Which Contain ANY Text

Download Error Fixer (Free)

I have found a solution, courtesy of Gord Dibben's contribution to
 
"Conditional Format if Not Formula" at
http://www.pcreview.co.uk/forums/conditional-format-if-not-formula-t3762736.html
 
For completeness, I didn't try Bernie Dietrick's approach because Gord's worked  - thank you Gordl
 
Relevant section reproduced below:
 
One more method for Gary.

Copy this UDF to a general module in your workbook.

Function IsFormula(Cell)
IsFormula = Cell.HasFormula
End Function

Select the cells and Format>CF>Formula is

=NOT(IsFormula(cellref))

Gord Dibben MS Excel MVP

Thanks,
 
M

If you failed to fix it, another option is to call Microsoft and walk through the process with a technician. To do this, dial "1-800-936-5700". Explain the error that you received and the steps in the update process during which it was displayed. The technician will be able to walk through the steps from getting a new product key to finishing the installation.

Recommended Method to Repair the Problem: Conditional Formatting Cells Which Contain ANY Text:

How to Fix Conditional Formatting Cells Which Contain ANY Text with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your computer.  Run it, and it will scan your computer. The junk files 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 Repair part is finished, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in this software. Like dll downloading, windows updating and print spooler error repair.


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: How to Fix Problem - conditional formatting based on number of characters in field?,Tech Support: computer shuts off suddenly at windows 8 logo,Solution to Error: Computer turns off unexpectedly,[Solution] Computer will shutdown\/restart on its own!,Fast Solution to Problem: Computer closes down turns off. only since windows 7 put on,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