2.Read Range Workbook (dt_excelValidation) and Assign (dt_primaryRows) to extract the primary rows from the Input Excel by considering the condition you said “process only the rows where Column B has a value and is not empty.”
dt_primaryRows = dt_excelValidation.AsEnumerable().
Where(Function(row) Not String.IsNullOrWhiteSpace(row(1).ToString())).
CopyToDataTable()
3.Invoke Code to identify the cells to colour. The output is a list of cell IDs:
The code:
For Each row As DataRow In dtPrimary.Rows
Console.WriteLine("Trying to find row in dtFull: " & row(1).ToString())
Dim rowIndex As Integer = -1
For i As Integer = 0 To dtFull.Rows.Count - 1
If dtFull.Rows(i)(1).ToString().Trim() = row(1).ToString().Trim() Then
rowIndex = i
Exit For
End If
Next
Console.WriteLine("Row index in dtFull: " & rowIndex)
If rowIndex = -1 Then Continue For
Dim excelRow As Integer = rowIndex + 2 ' Add offset for header
' --- Column C ---
Console.WriteLine("Column C value is : " & row(2).ToString().Trim())
If row(2).ToString().Trim().Equals("Recurring JV") Then
cellsToColor.Add("C" & excelRow.ToString())
End If
' --- Column E ---
Console.WriteLine("Column E value is : " & row(4).ToString())
Dim dateVal As DateTime
Dim dateString As String = row(4).ToString().Trim()
If DateTime.TryParseExact(dateString, "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, dateVal) Then
If dateVal < FromDate OrElse dateVal > ToDate Then
cellsToColor.Add("E" & excelRow.ToString())
End If
End If
' --- Column J ---
Console.WriteLine("Column J value is : " & row(9).ToString().Trim())
If Not row(9).ToString().Trim().Equals("Approved", StringComparison.OrdinalIgnoreCase) Then
cellsToColor.Add("J" & excelRow.ToString())
End If
' --- Column L ---
Console.WriteLine("Column L value is : " & row(11).ToString().Trim())
If Not row(11).ToString().Trim().Equals("Eligible for SAP Posting", StringComparison.OrdinalIgnoreCase) Then
cellsToColor.Add("L" & excelRow.ToString())
End If
Next
4.Excel Application Scope targeting the input excel, and use a For each to iterate through the list of Cells, and the use Set Range Color activity to colour the cells. Refer below screenshot:
Here, is some reference from the Log messages, to show you the Console.Write from the Invoke Code, and also the Locals Panel. This will give you clarity:
