Coloring all the cells where equals "0"

example.xlsx (25.9 KB)
Hi, everyone. I’m facing this problem for so long. Here’s the excel sheet which will generated by my another program.
The problem I’m facing was how to highlight the cells which was equals = 0. There’s 39 columns and every week have different number of columns. So, it is hard for me to do manually using for each row. :joy:
Any suggestions? Regards for your ideas.

@Wei_Ji_Kwok Fill excel cell color according to condition

Hi @Wei_Ji_Kwok,

What about below workflow

Input
image

Output
image

Workflow

Code

Dim XLApp As Microsoft.Office.Interop.Excel.Application
Dim XLWb As Microsoft.Office.Interop.Excel.Workbook
Dim XLWbs As Microsoft.Office.Interop.Excel.Worksheet
Dim XLRange As Microsoft.Office.Interop.Excel.Range
Dim XlRange2 As Microsoft.Office.Interop.Excel.Range
Dim newcell As Microsoft.Office.Interop.Excel.Range
Dim newcelll As Microsoft.Office.Interop.Excel.Range
Dim totalrows As Int32,i As Int32
Dim rangeArray(), tempRange,startCol,endCol,Startcoll,endcoll As String
XLApp=New Microsoft.Office.Interop.Excel.ApplicationClass
XLWb=XLApp.Workbooks.Open(in_FileName)
XLWbs=CType(XlWb.Worksheets(in_SheetName),Microsoft.Office.Interop.Excel.WorkSheet)
totalrows=XlWbs.UsedRange.Rows.Count

startCol=in_Range.Split(CChar(“:”))(0)
endCol=in_Range.Split(CChar(“:”))(1)

XLRange= XlWbs.Range(startCol+“2”,endCol+totalrows.ToString)

For Each newcell In XLRange.Cells
If newcell.Value.tostring <>“0” Then
Else
newcell.Interior.ColorIndex=6
End If
Next newcell
XLWb.Save()
XLWb.Close()
XLApp.Quit()

Best,

1 Like

@Wei_Ji_Kwok

Check the condition if it is 0 then fill the color as you want, If not 0 then it will go to else direction we have set range color activity use this in with excel scope it will work.

Thnaks
Varun

1 Like

Hello @Wei_Ji_Kwok

You can use If activity to check the condition and then use Format cell activity. Can check the below video.

Thanks

Thanks a lot everyone. Finally solved the problem

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.