Conditional formating

Matti · May 8th, 2002, 09:49 AM

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

Mark W. · May 8th, 2002, 10:41 AM

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 ]

Nimrod · May 8th, 2002, 10:43 AM

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 ]

Matti · May 8th, 2002, 10:47 AM

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

Nimrod · May 8th, 2002, 10:50 AM

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

Nimrod · May 8th, 2002, 10:54 AM

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 ]

Brian from Maui · May 8th, 2002, 11:01 AM

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.

Matti · May 8th, 2002, 11:23 AM

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.

Matti · May 8th, 2002, 11:37 AM

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,

Nimrod · May 8th, 2002, 11:51 AM

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.

May 8th, 2002, 10:50 AM	#5
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	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 __________________ <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 8th, 2002, 09:49 AM	#1
Matti New Member Join Date: May 2002 Posts: 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

May 8th, 2002, 10:41 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

May 8th, 2002, 10:43 AM	#3
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	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 ]

May 8th, 2002, 10:47 AM	#4
Matti New Member Join Date: May 2002 Posts: 8	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

May 8th, 2002, 10:54 AM	#6
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	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 ]

May 8th, 2002, 11:01 AM	#7
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	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.

May 8th, 2002, 11:23 AM	#8
Matti New Member Join Date: May 2002 Posts: 8	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.

May 8th, 2002, 11:37 AM	#9
Matti New Member Join Date: May 2002 Posts: 8	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,

May 8th, 2002, 11:51 AM	#10
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	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.