Conditional Formatting

hardeep.kanwar

hardeep.kanwar

Well-known Member
Joined
Aug 13, 2008
Messages
691
Hi! Experts

2 days Before one of my friend ask me this question.

Her Question is Mentioned Below:

She have a spreadsheet that have many formulas.
If anybody types over it with a value, can the font change to blue or any other so i can see that they typed over the formula.
I need the formatting rule to apply to various cells throughout the
spreadsheet. the formulas are not identical.

And don't want Macro Or any type of Code.



So, It is Possible or not

Thanks in Advance
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
This is probably as close as you will get.

In the name manager create a range named "HasFormula" which is equal to

=GET.CELL(48,INDIRECT("RC",FALSE))

Then, select the entire range in question, go to conditional formatting and create a condition

=HasFormula

select whatever formatting you desire and save.

This will apply your formatting to any cells in the range which are formulas.

Credit goes, I believe, to Excel Guru's book for this trick.
 
Upvote 0
If you set up the named range as I suggested and select the cells that contain formulas using the conditional formula =NOT(HasFormula) using Blue text as the condition, any time someone types over the formula you will see blue text.

If this isn't what you are trying to achieve, please try to clarify.
 
Last edited:
Upvote 0
=GET.CELL(48,INDIRECT("RC",FALSE))
Click to expand...
You can use A1 reference style with this.
The formula will be relative to whatever cell you apply the formatting to.

=GET.CELL(48,A1)

Just make sure that cell A1 is selected (the active cell) when you define the name.
 
Upvote 0
Whichever way floats your boat! :cool:

This one will work as is on multiple sheets:

=GET.CELL(48,INDIRECT("RC",FALSE))

To make the other one work on multiple sheets:

=GET.CELL(48,!A1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Using conditional formatting to highlight the row of a cell containing specific text.
Replies
4
Views
485
etaf
etaf
K
  • Question
Conditional Formating - Dynamic "not between"
Replies
3
Views
334
dreid1011
dreid1011
W
  • Solved
Copying Conditional Formatting with relative values
Replies
9
Views
658
weeksy
W
D
  • Question
I am trying to apply a new conditional formatting rule and it is not working
2
Replies
10
Views
468
Peter_SSs
Peter_SSs
J
  • Question
Conditional formatting based on value of another cell
Replies
3
Views
337
etaf
etaf

Forum statistics

Threads
1,216,119
Messages
6,128,944
Members
449,480
Latest member
yesitisasport

Share this page

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