How To Filter Excel Column By Cell Color?

News Knowledge Base
1

How to filter Excel column by cell color ?

Approach 1: Filter table activity will filter the column based on the input values only and not based on the color.

The workaround to achieve the desired behavior is:

  1. Insert a Column in Excel named color (Manually)
  2. Get the datatable using a read range activity.
  3. Then iterate over the datatable using foreach row.
  4. Within the loop, find the index of each row. (index of the row) + 2 will give the excel row number.Using this, the row reference can be obtained
  5. Get the color of the cell using "Get cell color" activity
  6. Post knowing the color of the cell, update the color name in the current row’s color column in the excel
  7. After the loop execution, use Filter table activity and filter the required color by passing the color name as the input.



Approach 2: UiAutomation can be performed on Excel application, this can also be used in the Excel spreadsheet which is not in table format.

  1. Use "Select Range" activity to select column header Example:- A1
  2. Use "Send Hotkey" Activity to perform shortcut to open Filter popup
  3. Perform clicks on "Filter by Color" -> Color element .