Delete value only from colored cell in an excel

[RamboRocky]

Hi All,

I want to delete value for colored cell from a specified range. Please see screenshot below.

P

[ashokkarale]

@RamboRocky,

Use Excel Process Scope, Iterate through whole data range. While iterating, use Get Cell Color activity to get the cell color.

Add condition to check color and delete the required values.

Thanks,
Ashok

[mkankatala (Mahesh Kankatala)]

Hi @RamboRocky

Use the below macros in the Invoke vba activity, follow the below steps,
→ Use the Excel Process scope activity inside of it, insert the Use excel file activity and give the path of the file to it.
→ Inside Use excel file activity insert the Invoke vba activity.
→ Take a Notepad and save the below code,

Sub DeleteValuesInRedCells(sheetName As String, rangeAddress As String)
    Dim ws As Worksheet
    Dim cell As Range
    Set ws = ThisWorkbook.Sheets(sheetName)
    
    For Each cell In ws.Range(rangeAddress)
        If cell.Interior.ColorIndex = 3 Then ' 3 is the ColorIndex for red
            cell.Value = ""
        End If
    Next cell
End Sub

→ In the Invoke vba activity, give the path of the Notepad file to the Code file path field and give the DeleteValuesInRedCells in method name field.
→ Click the Add argument to pass the Sheet name and rangeaddress.

It will automatically delete the cell values in red color.

Hope it helps!!

[RamboRocky]

Hi how do I add sheetName and rangeAddress? Should I pass it as variable in VBA argument? And how do I add range.

Is this correct so far?

[mkankatala (Mahesh Kankatala)]

Yes correct, The sheetName and rangeAddress both are variables and you stored the value init, right.

If you stored the sheet name like Sheet1 in sheetName and requried Excel range like A1:G40 like this in rangeAddress, then its correct.

Hope it helps!!

[RamboRocky]

Gives me this error even though the range should be correct.

[mkankatala (Mahesh Kankatala)]

I am checking wait… @RamboRocky

[mkankatala (Mahesh Kankatala)]

It’s working for me… @RamboRocky

What you have initialized for both arguments sheetName and rangeAddress, can you show me once.

[RamboRocky]

[mkankatala (Mahesh Kankatala)]

both variables datatypes should be String, you gave Object datatype change itto string in the Variables panel… @RamboRocky

[RamboRocky]

Still does the same. Can you please send me your workflow so I can compare? Thanks

[mkankatala (Mahesh Kankatala)]

Okay @RamboRocky

Check the below workflow for better understanding,
Sequence2.xaml (15.4 KB)

Hope it helps!!

[RamboRocky]

Thanks mate. Working Perfectly now!

Kind Regards

[mkankatala (Mahesh Kankatala)]

It’s my pleasure… @RamboRocky

Happy Automation!!