Highlight field that are not derived from a formula

[Julian Bessenroth]

Hi Folks,

I've got a question/problem I can not solve on my own.

I want to format a cell this way that the background becomes e.g. red
if the content is not defined via a reference.


e.g.

Cell Content Highlight
A1 =B3 no
A2 =C4 no
A3 abc yes
A4 =B3 no
A5 bla yes

Can anyone give me a hint how to do so?

Many thanks in advance

regards

Julian

 

[Sheeloo]

Try this
Edit|Go To|Special
Click on Constants

This will highlight all cells which do not have a formula... You can then
format them as you please.

 

[Teethless mama]

Select your range > hit F5 > Special > Constants > OK > select your
background color

 

[Julian Bessenroth]

Try this
Edit|Go To|Special
Click on Constants

This will highlight all cells which do not have a formula... You can then
format them as you please.

Hi guys,

thanks for being responsive. As I see my question was not fully
accurate. This would lead to a static formating. I'd like the cells to
change on condition if I enter a constant. So if I change e.g. "=B2"
to "bla" is should change. Is this possible with conditional
formatiing? I did not find a way so far.

Again, thanks in advance

Julian

 

[Sheeloo]

Then you need to use VBA. Let me know if you need help with that.

 

[Julian Bessenroth]

Then you need to use VBA. Let me know if you need help with that.

Thanks Sheeloo,

if it is like this then I think I'll go for the first method.
Otherwise it'd be somewhat too sophisticated/overkill for what I
intend to do.

Thanks anyway.

Regards

Julian

 

[Sheeloo]

You are right, it is not worth the effort...

Recording a macro to highlight for the first time is easy... you run into
issues when you want to remove highlighting when constant changes to a
formula or vice versa.
One option is to write this into WorkSheet_Change macro like the one below

Private Sub Worksheet_Change(ByVal Target As Range)

Target.SpecialCells(xlCellTypeConstants, 23).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
End With
End Sub