figment222
New Member
- Joined
- Mar 6, 2015
- Messages
- 48
I have a range of column headers that is transposed from a list in column B. When a value from column B changes, I have a macro that will copy those values and transpose them into row 8, starting with R8. I want to prevent the user from changing the column headers.
I want the column and the headers to always match. I can't run the TransposeNames macro when the column headers change because it will trigger a cascade. BUT, if I could run the TransposeNames macro when the headers change IF the changes don't match, then I can avoid the cascade.
If they change the value in the header, it will trigger the macro to make the names match again. Changing that value will trigger the macro again, but because I have it nested in an IF statement that first compares the ranges, it won't trigger the cascade because the change resulted in a match. Does that make sense?
My question is how to write the If statement to run a macro only when column B and row 8 do not match.
In case it helps, here's the script for the TransposeNames Sub:
I want the column and the headers to always match. I can't run the TransposeNames macro when the column headers change because it will trigger a cascade. BUT, if I could run the TransposeNames macro when the headers change IF the changes don't match, then I can avoid the cascade.
If they change the value in the header, it will trigger the macro to make the names match again. Changing that value will trigger the macro again, but because I have it nested in an IF statement that first compares the ranges, it won't trigger the cascade because the change resulted in a match. Does that make sense?
My question is how to write the If statement to run a macro only when column B and row 8 do not match.
In case it helps, here's the script for the TransposeNames Sub:
Code:
Sub TransposeNames()
Dim SourceRange As Range
Dim TargetRange As Range
Dim Start As Long, Final As Long
Start = Range("B:B").Find("Feature Type", Range("B1")).Row
Final = Range("B:B").Find("End", Range("B" & Start)).Row
Set SourceRange = Range("B" & Start + 1 & ":B" & Final)
Set TargetRange = Range("R8")
SourceRange.Copy
TargetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Transpose:=True
TargetRange.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, Transpose:=True
TargetRange.FormatConditions.Delete
SourceRange.ClearOutline
Range("A7").Select
End Sub