Automating conditional formatting

[Chets]

Hi All

I have a spreadsheet that tracks thousands of operational metrics per month. I'd like to add conditional formatting, Red/Yellow/Green, based on threshold values for each metric. Due to the large number of metrics I want to avoid manually setting Conditional Formatting for each row and cell range. Is there a way to automate applying of Conditional Formatting?

Sample spreadsheet uploaded.

Thanks.

 

[Hui]

Firstly, you don't need VBA

Select the Range H5:S5000

Goto the Conditional Formatting, Clear Rules, Clear Selected Area

Now reapply the CF Rules but leave out the $ preceding the Row Number

eg: Cell Value <$G5 instead of <$G$5
Cell value >= $C5 instead of $C$5
etc

Excel automatically adjusts the rows 6:5000 for you

By leaving the $5 there you are forcing each row to refer to row 5