View Full Version : Excel conditional cell format

21-07-2004, 01:57 PM
I want to apply a conditional format to an entry cell, so that the cell contents are invisible or visible according to the nature of those contents.

Sounds a bit opaque? Lets try again: if the cell contains a formula, I want the cell to appear empty on screen or in print. If on the other hand the formula is replaced (manually) by a date, I would like the cell display to revert to normal visibility.

I have already experimented with various combinations of conditional formats, but can't seem to find the right combination of conditions to achieve the desired result. So far, either the cell contents stay visible no matter what they are, or they don't! Frustrating!

All suggestions/solutions very gratefully received.

21-07-2004, 02:26 PM
Your conditional format would simply need to set the font colour to the same as the background (e.g.white for a default background) to hide the contents.

As we have no idea what values are going to be entered to replace the formula, its hard to comment on the conditional format testing.

The problem as I see it, is that the formula itself does not have a testable value, only the result of the formula does?

21-07-2004, 02:43 PM
Hi, your right - what you want is very strange! :-)

I dont think you could achieve this with Conditional Formatting but you can with code. Right click the sheet then paste the following code which will turn the cells font from normal to white - presuming you have white as the background colour of your sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

For Each c In Target
If c.HasFormula = True Then
c.Font.ColorIndex = 2 'White
c.Font.ColorIndex = 0
End If
Next c
End Sub

21-07-2004, 02:47 PM
Damn too quick - should have said right click the sheet, select View Code then paste into the right hand window. Select Alt+Q to return to Excel sheet.

21-07-2004, 08:50 PM

The manual entry to the cell would be a date in dd/mm/yy format, ie the normal cell format itself is dd/mm/yy.

<The problem as I see it, is that the formula itself does not have a testable value, only the result of the formula does?> You are right, up to a point. The conditional test I have been trying to devise is along the lines of:

If the formula *itself* is in the cell, ie in the specific case "=max(e6:e26)", then the font colour should be white = invisible. If however a date has been entered into the cell, eg "21/07/04", then the font colour should be Black = visible

So as I see it at present, I am looking for a way to set that test/s as the conditions for a change in format.

Hope this clarifies the nature of the problem a little.

21-07-2004, 11:46 PM
You need a formula that will test whether there is a formula or not. There may be an old XL4Macro formula which does it but there is no standard formula within Excel that can check whether a formula exists in a cell. You need to use a coded solution such as I have posted above.

The code starts when you change a cell so select all the cells with values in your sheet then copy and paste back over itself. This will force the Worksheet_Change event and the code will do its stuff. You only have to do this once and from then on the cell will change colour as required.

22-07-2004, 08:41 AM
Thanks Parry

I'll try your piece of code and report back.

03-08-2004, 04:23 PM

I'm happy to report, after a week or so's operational use, that your piece of VBA consistently does exactly what I wanted it to. It's a very efficient way to control display of cell contents according to a set of rules, and working in concert with comparison operators and input rules.

In the test case as I explained at the outset, I wanted a row of cells to appear blank so long as each contained a formula, but to display normally only if and when a date was manually input in place of the formula. In addition, in the column below each such cell, the cell display formats are dependent on the content of the "header" cell.

The operation/appearance of the worksheet is now working perfectly, thanks to the VBA routine, so much so that I'm now thinking about other similar worksheets that this logic could be adapted to! It also encourages me to learn more about VBA myself......



03-08-2004, 07:47 PM
Excellent, Im glad it meets your needs. :-)

Another approach could have been to use a UDF (User Defined Function) since there isnt a standard formula that tells you whether a cell has a formula or not. The following UDF called CELLHASFORMULA returns true or false, with True meaning there is a formula in the cell.

Function CELLHASFORMULA(Cell) As Boolean
' Returns TRUE if cell has a formula
CELLHASFORMULA = Cell(1).HasFormula
End Function

You use this like any other formula ...

This code needs to go into a standard module rather than a sheet module where you placed the other code. To add the code to a Module do the following:-
1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu)
2. Select Insert|Module from the menu
3. Paste the code in the right-hand window
4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu)

Learning VBA is a nice skill to have and you can find plenty of help at http://www.mrexcel.com, http://www.theofficeexperts.com/forum/ and http://www.vbaexpress.com/forum/ if you get stuck.