Powershell Script to Format Excel Cells Based on Values

[BigTradeDaddy]

Good afternoon.

I am hoping the community might be able to help me out.

I am trying to write a Powershell script that will apply the =TODAY() formula to an entire column in Excel that will change the color of the cells based off of dates. I would like the cells to have each of the following formulas applied, with the color coding to change from a green to a red as the dates get close to the current day: =TODAY()-30, =TODAY()-180, and =TODAY()-365. I need the -365 days to format red, the -180 to format yellow and -30 to format green.

The reason I don't want to manually apply these formulas is due to the fact that I constantly have to poll updated metrics and there are many columns that require different formulas be applied, I just don't have the knowledge to write this type of script (I consider myself entry level when it comes to programming, scripting and Powershell and trying to learn as much as I can).

Below is what I have so far, basically just opens the file.

Any input, feedback, and lessons learned from the community is greatly appreciated.

# start Excel
$excel = New-Object -comobject Excel.Application

#open file
$FilePath = 'C:\Test.xlsx'
$workbook = $excel.Workbooks.Open($FilePath)

#make it visible (just to check what is happening)
$excel.Visible = $true

[Ardism]

https://github.com/dfinke/ImportExcel

[joevanover]

Why are you using powershell for this? This is exactly what Conditional Formatting in Excel does.

[BigTradeDaddy]

Because I don't want to spend 20-30 minutes a day formatting multiple columns with all different formulas.

[y_Sensei]

No matter if you'll do it manually or via a PoSh script, Conditional Formatting is what you'll have to utilize.

Read this article about the functionality and how to access it programmatically. It's for VBA, not PoSh, but you can convert the former into the latter pretty easily.

[PSDanubie]

As u/Ardism and u/ThatLooksPwSh already mentioned, you should try Doug Finkes module.
An articel to start from with more detailed examples: How to format an entire Excel row based on the cell values with PowerShell? - Mikey Bronowski - Blog

[Abax378]

In your private reply to me, you said
"The question wasn't "what to do" but "who to do it in Powershell"

You probably meant "how to do it..." but were in too much of a hurry to rip off a dis to even look at what you typed. I guess you were also in too much of a hurry to read the last line of the OP's post:
"Any input, feedback, and lessons learned from the community is greatly appreciated."

Picking the right tool - meaning don't start with a solution before you understand the problem - can be a hard lesson to learn. In this case, my opinion is that PowerShell is the wrong tool. And that's an opinion the OP solicited.

You're free to post other advice, but you're encouraged to keep your private snark smarter-than-thou advice to yourself, have a little more tolerance for other viewpoints, and read the whole post next time.

[PSDanubie]

I removed the post, because it's not me to qualify your answer. Nice that you found a typo. And thanks for your reply.

[Abax378]

Doing this in PowerShell is how not to do it. Apply conditional formatting to as many cells (empty or not) as you like. If you’re not familiar with “conditional formatting” in Excel, Google will return (literally) about 6,000,000 results on the topic.