Color code exported reports

[Ode2Joy]

When I view certain reports in Lansweeper (Patch Tuesday and W11 Readiness to name a couple), they are nicely color-coded as red or green depending upon if they pass or fail certain criteria.  When I export the reports to Excel, they are no longer color-coded, but the HEX code for the color used to display the color in the Lansweeper report is listed in a "backgroundcolor" column in the Excel file.  Is there any way to color-code the spreadsheet based on the values in this column?  I tried using Conditional Formatting to do this, but am just not getting very far.

[Ode2Joy]

Fantastic, and just what I needed.  I was getting stuck trying to make the 'Highlight Cells Rules' work instead of using a formula to determine.  Thanks a ton!

[rader · Accepted solution]

Run this conditional formatting on the sheet you exported.

Out of date conditional format:

• Rule Type: Use a formula to determine which cells to format: =IF($U2="#ffadad",1,0)
• Preview: Red fill with black text (more colors, custom, hex color #ffadad)
• Range: =$A$2:$U$71 (range of my sheet - adjust to your needs)

Up to date conditional format:

• Rule Type: Use a formula to determine which cells to format: =IF($U2="#d4f4be",1,0)
• Preview: Green fill with black text (more colors, custom, hex color #d4f4be)
• Range: =$A$2:$U$71 (range of my sheet - adjust to your needs)

These have to be added each time you create a report or you can create a blank sheet as a template then copy/paste the export results to the template each time you create the report from Lansweeper. You can change the custom colors to match what you need for better contrast on your excel template sheet.

Good luck.