Log In
Register
Home
Dashboards
Power Pivot
Power Query
June 7, 2019
Offline
I've created a file and I want to be able to change the cell color on the Master Order Form -worksheet when one of the users enters a new value in one of the worksheets called Blair and Jason
I found this VB on the web however I can't get it to work. I have attached my file that I'm working on.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
' Highlight the active cell
Target.Interior.ColorIndex = 8
Application.ScreenUpdating = True
End Sub
Thanks
See AnswerJuly 31, 2020
Offline
How about
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim RwOffset As Long
If Target.CountLarge > 1 Then Exit Sub
Select Case Sh.CodeName
Case "Sheet3": RwOffset = 1
Case "Sheet4": RwOffset = 9
Case Else: Exit Sub
End Select
Sheet1.Range(Target.Address).Offset(RwOffset).Interior.ColorIndex = 8
End Sub
This needs to go in the ThisWorkbook code module
Answers Post
Dashboards
Power Pivot
Power Query
June 7, 2019
Offline
Hi
This is great. I'm wondering if it's also possible to clear the cell fill if the user decides to revert back to the original value?
Thanks for your great help
July 31, 2020
Offline
To do that you would need to store all the changes that were made, along with cell references, to another sheet.
1 Guest(s)
