Conditional formatting

bobby440 

Conditional formatting Approach:

Click on "Conditional Formatting" in the "Styles" group.

Choose "New Rule" from the drop-down menu.

Select "Use a formula to determine which cells to format".

In the formula field, enter a formula to check the corresponding cells in Sheets 2, 3, and 4.

For example, if you want to check cell A1 in Sheet 2, the formula would be =Sheet2!$A$1<>"".

Specify the formatting you want to apply when the formula evaluates to TRUE.

Repeat these steps for each cell or range that you want to monitor.

Whenever you update a cell in Sheets 2, 3, or 4 with the specified color, it will automatically reflect in the "Master Data" tab based on the conditional formatting rules.

VBA Approach:

Here's how to use the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim masterSheet As Worksheet
    Dim dataSheets As Variant
    Dim dataSheet As Worksheet
    Dim cell As Range
    
    Set masterSheet = ThisWorkbook.Worksheets("Master Data")
    dataSheets = Array("Sheet2", "Sheet3", "Sheet4") ' Update with your sheet names
    
    ' Check if the changed cell is in any of the data sheets
    If Not Intersect(Target, Sheets(dataSheets)) Is Nothing Then
        Application.EnableEvents = False ' Disable event handling to avoid triggering another change
        
        ' Loop through each data sheet
        For Each dataSheet In Sheets(dataSheets)
            ' Loop through each cell in the changed range
            For Each cell In Target.Cells
                ' Check if the cell has a specific color
                If cell.Interior.Color = RGB(255, 0, 0) Then ' Update with the color you want to monitor
                    ' Update the corresponding cell in the Master Data sheet
                    masterSheet.Range(cell.Address).Value = cell.Value
                    ' You can also apply formatting or other actions here if needed
                End If
            Next cell
        Next dataSheet
        
        Application.EnableEvents = True ' Enable event handling
    End If
End Sub

Press ALT + F11 to open the Visual Basic Editor.

In the Project Explorer window, find the sheet where you want the changes to be reflected (e.g., "Master Data").

Double-click on the sheet module to open the code window.

Copy and paste the above code into the code window.

Modify the dataSheets array to include the names of your data sheets.

Update the color condition in the line If cell.Interior.Color = RGB(255, 0, 0) Then to the desired color you want to monitor.

Save the workbook and test the code by making changes to the specified color in the data sheets. The changes should automatically reflect in the "Master Data" sheet.

Make sure to save your workbook in a macro-enabled format (e.g., .xlsm) to retain the VBA code.

Both approaches have their advantages. The conditional formatting approach is simpler and doesn't require VBA programming knowledge, while the VBA approach provides more flexibility and allows for customized actions based on the color changes.

Choose the approach that best suits your requirements and familiarity with Excel functionalities.