How to use conditional highlighting on two different workbooks using VBA in Excel and Studio

Help Something Else
, ,
1

I have two workbooks and I’m trying to go through them and highlight the following.

  1. In each row where an error is identified, I want to highlight the (“Errors”) column, the (“Employee”) column as well as the column with the specified error.

Right now, in Excel VBA I’m trying this but it doesn’t work properly for both sheets since the location of the (“Employee”) column is different in each workbook. In WB1 it’s working properly but not in WB2.

Any help would be greatly appreciated!

'Set wb = Workbooks.Open(folderPath & fileName)
Debug.Print filePath
Set wb = Workbooks.Open(filePath)
'For Each ws In Workbooks(filePath).Sheets
For Each ws In wb.Sheets
'ws.Application.ActiveWindow.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlNormal

    lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = ws.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
     Debug.Print lastRow
     Debug.Print lastCol
     
     For rowNum = 1 To lastRow
        For colNum = 1 To lastCol
            Debug.Print ws.Cells(rowNum, colNum).Value
            Debug.Print (InStr((ws.Cells(rowNum, colNum).Value), ws.Cells(1, colNum).Value))
            
            If rowNum <> 1 Then
                If InStr((ws.Cells(rowNum, 1).Value), ws.Cells(1, colNum).Value) > 0 Then
                    ws.Cells(rowNum, colNum).Interior.Color = RGB(255, 255, 0)
                    ws.Cells(rowNum, "A").Interior.Color = RGB(255, 255, 0)
                    ws.Cells(rowNum, "H").Interior.Color = RGB(255, 255, 0)
                    Debug.Print "Errors: "; ws.Cells(rowNum, 1).Value & "Color this Cell: " & ws.Cells(rowNum, colNum).Address & " with Value: " & ws.Cells(rowNum, colNum).Value
                End If
            End If
2

@related-robot,

It’s not really related to UiPath. You can check this on VBA forums for solutions as there would be more experts than here for VBA.

Thanks,
Ashok :slight_smile: