Conditional formatting - linked workbooks

[Sandy82]

Hi Excel Experts

I am creating several workbooks, one will be mine, and the others linked to
mine and sent to people for them to revise and return to me to enable mine to
automatically update, (the linked cells will contain blanks or numbers).
Please could you tell me if there is a formula I could use in conditional
formatting to enable me to identify in my workbook which linked cells will
have changed, i.e. when a number is revised or added to blank cell the font
colour changes , or if the number is deleted the blank cell changes colour?

I hope I have explained myself correctly

Many thanks

 

[Shane Devenshire]

Hi Sandy,

This is not a conditional formatting issue. You could consider an
Worksheet_Change macro. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then
Target.Interior.ColorIndex = 6
Else
Target.Font.ColorIndex = 3
End If
End Sub

However, this will format the cell if the user changes something and then
changes it back to the original value, for example they use undo.

Also, as written this works with every cell in the worksheet. If you want
to control which cell you need to define the range by adding something like

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Target, [MyRange])
If Not isect Is Nothing Then
If Target = "" Then
Target.Interior.ColorIndex = 6
Else
Target.Font.ColorIndex = 3
End If
End If
End Sub

This code needs to be added to the Sheet module of any sheets where you want
it to work. Note that MyRange is a range name you have defined in the
spreadsheet.

You can compare two workbooks for changes using one of the VBA add-ins you
can find on the Web - Google "Compare workbooks" and skip the ones that use
Excel's built in command Windows, Compare...

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[Sandy82]

Hi Shane

Many thanks for this I will try the macro out first.

Best regards
Sandy

Hi Sandy,

This is not a conditional formatting issue. You could consider an
Worksheet_Change macro. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then
Target.Interior.ColorIndex = 6
Else
Target.Font.ColorIndex = 3
End If
End Sub

However, this will format the cell if the user changes something and then
changes it back to the original value, for example they use undo.

Also, as written this works with every cell in the worksheet. If you want
to control which cell you need to define the range by adding something like

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Target, [MyRange])
If Not isect Is Nothing Then
If Target = "" Then
Target.Interior.ColorIndex = 6
Else
Target.Font.ColorIndex = 3
End If
End If
End Sub

This code needs to be added to the Sheet module of any sheets where you want
it to work. Note that MyRange is a range name you have defined in the
spreadsheet.

You can compare two workbooks for changes using one of the VBA add-ins you
can find on the Web - Google "Compare workbooks" and skip the ones that use
Excel's built in command Windows, Compare...

Cheers,
Shane Devenshire
Microsoft Excel MVP

Hi Excel Experts

I am creating several workbooks, one will be mine, and the others linked
to
mine and sent to people for them to revise and return to me to enable mine
to
automatically update, (the linked cells will contain blanks or numbers).
Please could you tell me if there is a formula I could use in conditional
formatting to enable me to identify in my workbook which linked cells will
have changed, i.e. when a number is revised or added to blank cell the
font
colour changes , or if the number is deleted the blank cell changes
colour?

I hope I have explained myself correctly

Many thanks

 

[Sandy82]

Hi Shane,

Once again many thanks for your help, the macro works really well but could
you possibly help me with another problem - is there a macro I could use in
the spreadsheet with the links so that it picks up the format changes from
the linked cells?
Sandy

Hi Shane

Many thanks for this I will try the macro out first.

Best regards
Sandy

Hi Sandy,

This is not a conditional formatting issue. You could consider an
Worksheet_Change macro. Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then
Target.Interior.ColorIndex = 6
Else
Target.Font.ColorIndex = 3
End If
End Sub

However, this will format the cell if the user changes something and then
changes it back to the original value, for example they use undo.

Also, as written this works with every cell in the worksheet. If you want
to control which cell you need to define the range by adding something like

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Target, [MyRange])
If Not isect Is Nothing Then
If Target = "" Then
Target.Interior.ColorIndex = 6
Else
Target.Font.ColorIndex = 3
End If
End If
End Sub

This code needs to be added to the Sheet module of any sheets where you want
it to work. Note that MyRange is a range name you have defined in the
spreadsheet.

You can compare two workbooks for changes using one of the VBA add-ins you
can find on the Web - Google "Compare workbooks" and skip the ones that use
Excel's built in command Windows, Compare...

Cheers,
Shane Devenshire
Microsoft Excel MVP

Hi Excel Experts

I am creating several workbooks, one will be mine, and the others linked
to
mine and sent to people for them to revise and return to me to enable mine
to
automatically update, (the linked cells will contain blanks or numbers).
Please could you tell me if there is a formula I could use in conditional
formatting to enable me to identify in my workbook which linked cells will
have changed, i.e. when a number is revised or added to blank cell the
font
colour changes , or if the number is deleted the blank cell changes
colour?

I hope I have explained myself correctly

Many thanks