Create Conditional Formatting Rules

Explain how to set up an Excel conditional formatting rule for the range [Specify cell range, e.g., D2:D100] that does the following: [Describe the rule, e.g., 'Highlights cells with a value greater than 500 in green', 'Highlights rows where the date in Column A is within the next 7 days', 'Uses an icon set based on values compared to the average of the range', 'Highlights duplicate values within the range']. Specify the desired formatting and if a custom formula is likely needed, provide the formula.