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

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.

"Julian Bessenroth" wrote:

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

On 11 Okt., 00:47, Sheeloo wrote:
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

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

"Julian Bessenroth" wrote:

On 11 Okt., 00:47, Sheeloo wrote:
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

On 11 Okt., 01:41, Sheeloo wrote:
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

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

"Julian Bessenroth" wrote:

On 11 Okt., 01:41, Sheeloo wrote:
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

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


"Julian Bessenroth" wrote:

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