Conditional formatting

bobby440 

Here is an example with VBA like i send it in other message.

Just hoping this is the desired result. If not, please provide more detailed and step-by-step (cell by cell, sheet by sheet) instructions for the project.

Option Explicit

Sub ConsolidateData1()
    Dim wsMaster As Worksheet
    Dim ws As Worksheet
    Dim lastRowMaster As Long
    Dim lastRow As Long
    Dim lastCol As Long
    Dim companyColMaster As Long
    Dim companyCol As Long
    Dim companyRowMaster As Long
    Dim companyRow As Long
    Dim company As String
    
    ' Set reference to the master worksheet
    Set wsMaster = ThisWorkbook.Worksheets("Master")
    
    ' Find the last row and column in the master worksheet
    lastRowMaster = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row
    lastCol = wsMaster.Cells(1, wsMaster.Columns.Count).End(xlToLeft).Column
    
    ' Find the column number for the "company" column in the master worksheet
    companyColMaster = wsMaster.Rows(1).Find(What:="company", LookIn:=xlValues, LookAt:=xlWhole).Column
    
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Skip the master worksheet
        If ws.Name <> "Master" Then
            ' Find the last row in the current worksheet
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            
            ' Find the column number for the "company" column in the current worksheet
            companyCol = ws.Rows(1).Find(What:="company", LookIn:=xlValues, LookAt:=xlWhole).Column
            
            ' Loop through all rows in the current worksheet (skipping the header row)
            For companyRow = 2 To lastRow
                ' Get the name of the company in the current row
                company = ws.Cells(companyRow, companyCol).Value
                
                ' Find the row number for this company in the master worksheet
                companyRowMaster = wsMaster.Columns(companyColMaster).Find(What:=company, LookIn:=xlValues, LookAt:=xlWhole).Row
                
                ' Copy data from the current row to the corresponding row in the master worksheet
                ws.Range(ws.Cells(companyRow, 2), ws.Cells(companyRow, lastCol)).Copy _
                    Destination:=wsMaster.Cells(companyRowMaster, 2)
            Next companyRow
        End If
    Next ws
End Sub

NikolinoDE

I know I don't know anything (Socrates)

Was the answer useful? Mark them as helpful!

This will help all forum participants.

bobby440 

Your request includes some ambiguities, and you showed no data or colors that could help resolve them.

1. What do you mean by "if I update any cell … with a color"? Are you saying you manually change the background color (highlighting)? Or the text color (foreground color)? Or that you manually change a value, whose text color and/or background color is then affected, either by conditional formatting or a condition-based custom format?
2. What do you mean by "i need the same to reflect in the master data tab"? The same value? The same color? Both? The answer is related to…
3. Are non-blank entries for a company and week mutually exclusive across the country-specific worksheets? If not, are the values summed, or maybe appended? Do summed/appended values on the Master sheet colored based on values in the same way as on the country-specific sheets?

The VBA code shown in an earlier reply is misleading/nonfunctional:

• The masterSheet reference uses the wrong literal. Similarly for the entries assigned to dataSheets. However…
• The dataSheets array is pointless; a VBA collection (such as the Sheets object) cannot use an entire array as an index.
• The cell.Interior.Color property applies to a manually-assigned background color, but its value is not updated by conditional formatting.
• If you can come up with a valid Worksheet_Change event handler, you will need such an event handler (at least part of it; some code could be "shared") in each of the country-specific worksheets.

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.