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
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
