Conditional formating

Matti

New Member
Joined
May 7, 2002
Messages
8
Hi,

Hope someone can help here - without having to use VB or Macro is it possible to pre-define a different format to cells that contain formulas to distinguish them from cells the contain manually entered values? (for example :each time I enter a formula the font colour will automatically be red and if a value is entered font colour will be black)

Many tks
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Matti, I trust that you're aware that you can examine the contents of a cell directly by toggling Control+` (see the ~ key). No formatting required...
This message was edited by Mark W. on 2002-05-08 09:46
 
Upvote 0
Hi Matti:

Preamble: Usually the way to do conditional formating is through the Conditional Format window found by going to the Format drop down menu. Unfortunately it only test for the conditions of the values in the cells and not if there is a formula or not. ...At least as far as I know.

SOLUTION:
However I've solved your problem by writting the following code to be inserted in as VB code:

If Target.HasFormula Then
Target.Font.ColorIndex = 3 'red
Else
Target.Font.ColorIndex = 1 'black
End If

LOCATION:
This code needs to be pasted into a procedure called: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

This is a procedure that fires every time there is a cell change in your workbook.
To find this procedure perform the following steps.
1- select "tools" drop down menu
2- select "macro"
3- select "visual basic editor"
4- View the upper left hand corner of editor to see project window
5- in project window double click on "This workbook" object
6- View the large right code window
7- Select "workbook" from the code window drop down.
8- Select "sheet change" from the right code window drop down
9- the code window should display
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

10- copy the code:
If Target.HasFormula Then
Target.Font.ColorIndex = 3 'red
Else
Target.Font.ColorIndex = 1 'black
End If

11- the final result should be :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.HasFormula Then
Target.Font.ColorIndex = 3 'red
Else
Target.Font.ColorIndex = 1 'black
End If

End Sub


PLEASE let me know if this works for you.
This message was edited by Nimrod on 2002-05-08 09:44
 
Upvote 0
Hi Mark and many tks for your reply,

I do know that but the purpose of the conditional formatting I mentioned is to highlight at a glance to the unassuming user of the spreadsheet where formulas are located and where a manual input is required.

Tks again
 
Upvote 0
A little quicker way of installing above is to:
This is a procedure that fires every time there is a cell change in your workbook.
To find this procedure perform the following steps.
1- select "tools" drop down menu
2- select "macro"
3- select "visual basic editor"
4- View the upper left hand corner of editor to see project window
5- in project window double click on "This workbook" object
6- Select the large right code window

Paste the following complete procedure:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.HasFormula Then
Target.Font.ColorIndex = 3 'red
Else
Target.Font.ColorIndex = 1 'black
End If

End Sub
 
Upvote 0
Matti:
As far as directing the user to the proper input cells might I suggest locking the cells that contain forumlas.

By locking the forumla cells two things occur:
1: When a user hits enter the cursor jumps to the next cell that allows input
2: Users are encapable of deleteing formulas by mistake

Note: not only can the formula cells be locked but you can also hide the formula from view at the same time.
This locking and hiding of forumlas leads to a much cleaner worksheet.. and more professional.

_________________

NOTE: Testing performed on Win2K utilizing Office 2000. Solutions may not work on other versions.
This message was edited by Nimrod on 2002-05-08 09:55
 
Upvote 0
Matti,

In addition to Nimrod's suggestion of locking and hiding the formulas, you might want to format the input cell. That is cell color red, until the user inputs data, then changes to original pattern.
 
Upvote 0
Hi Nimrod,

It works fantastically well. Exactly what I wanted.

As I'm not familiar with VBA however very intrigued by the potential of it two things:

1. Could you pls "translate"/explain the first line in your statement "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) i.e. how do I read it?, what does it mean? What follows is straightforward.

2. What’s the best way to get the basics of VBA for Excel - i.e. recommended book/URL

Your help is much appreciated.

Matti.
 
Upvote 0
Nimrod,

One more thing if I may - the code you've given me is triggered whenever you make an entry - how do I apply the same rule to formulas/values already entered.

Tks,
 
Upvote 0
How many sheets and what kind of ranges are we talking about ?

As far as learning goes you might want to present that as an individual question on the discussion board. I feel there are alot more qulified people here that can present you with a more complete answer. After all I'm a nimrod :). .... I'm just self taught by going through the Excel help files.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top