Macros modify, linked cells, formatting

jknowlden

New Member
Joined
Jan 28, 2011
Messages
4
Basically, I have a workbook with several sheets. Sheet 1 is a "master" sheet named UPDATE DRAWING INFO and it contains a ton of numeric and text values. The other sheets reference cells from this "master" sheet. This way, a change on the master sheet is reflected in all the other sheets.

When a value on the "master" sheet changes, all the cells (on the other sheets) that are linked to the changed cell (on the "master" sheet) will change background and font color. This is a visual flag that lets me know something has changed when I am looking at sheets other than the "master" sheet.

I'm familiar with some programming (MatLab and C) but just started using VBA. The macro below is what I'm currently using but it has a few bugs and I want it do do a little more...

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 6
Dim sht As Worksheet
Dim cell As Range
Dim RelForm As String
Dim Pos As Integer
If Target.Count = 1 Then
For Each sht In Worksheets
If Not sht.Name = "UPDATE DRAWING INFO" Then
For Each cell In sht.UsedRange
If cell.HasFormula Then
RelForm = Replace(cell.Formula, "$", "")
Pos = InStr(RelForm, Target.Address(0, 0))
If Pos > 0 And Not IsNumeric(Mid(RelForm, Pos + Len(Target.Address(0, 0)), 1)) And InStr(cell.Formula, ActiveSheet.Name) Then
cell.Interior.ColorIndex = 3
cell.Font.ColorIndex = 6
End If
End If
Next cell
End If
Next sht
MsgBox "LINKED CELLS HAVE BEEN HIGHLIGHTED - DOUBLE CLICK CELLS TO REMOVE FLAG"
End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Interior.ColorIndex = 19
ActiveCell.Font.ColorIndex = 0
Cancel = True
End Sub

What I want:

If a cell is empty and I enter data it makes the format change, which I dont want. I only want the formatting to occur if I change a cell that already had data in it. My guess is I can add an If statement that says not do do anything if the cell was originally blank. Or better yet, can I specify which cells/range the macro to runs on...

Also, for the linked pages, when the formatting changes, is there a way to also change the formatting for the cells that are adjacent (in the same row)? This would also need to happen to only cells that have an entry...

Lastly, the last bit of the code titled "Worksheet_BeforeDoubleClick" changes the format to a desired format (set to the original format) after double clicking on the cell. This way after a changed value is flagged, all I have to do is double click on it to remove the new formatting. The problem is if I click on an empty cell it also changes the format, which I dont want. Also, normally, when you double click a cell, a cursor appears in the cell and you can enter data, and this no longer happens...

Any suggestions would be greatly appreciated...

- Jason
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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