Description:

Conditional formatting is a popular feature and is a great way to easily identify cells with a range that meet some criteria. However, users often want to create conditional formatting rules that go beyond comparing a cells value to a single value or a single cell reference row or column comparisons are commonly requested operations.

But sometimes the built-in formatting rules dont go quite far enough. Adding your own formula to a conditional formatting rule gives it a power boost to help you do things the built-in rules cant do.

Now, well introduce some scenarios to help us and do it better.

Scenario 1: How to highlight the data between two special dates

Description: 
In some business report, we want to highlight the important data between two special dates, we may use the conditional formatting to reach the goal quickly.

Solution:
Click Home tab>Conditional Formatting >New Rule>Use a formula to determine which cells to format > Add the formula "=AND(F$6>=$D$7,F$6<=$E$7)"

URL:

http://social.technet.microsoft.com/Forums/office/en-US/6bccb179-c743-4b85-9f3f-c09b4ca09ca7/conditional-formatting-between-dates-usng-a-different-cell?forum=excel#2beb12b1-bc85-48bc-8022-1a666e4cb4cb

Scenario 2: How to apply conditional formatting on a range of cells in a table that is based on a data connection

Description:
When you want to format a cell based on the value of a different cell, for example to format a report row based on a single columns value, you can use the conditional formatting feature to create a formatting formula. This post explores the details of formatting a cell or range based on the value in another cell.

Solution:
Click Home tab>Conditional Formatting >New Rule>Use a formula to determine which cells to format > Add the formula =$A1="X"

URL:

http://social.technet.microsoft.com/Forums/en-US/36a2e7f0-7a79-438f-9613-477d3d918f5e/applying-conditional-formating-on-a-range-of-cells-in-a-table-that-is-based-on-a-data-connection?forum=excel

http://social.technet.microsoft.com/Forums/office/en-US/46bfa2a0-a40a-4229-967d-9de419376cb9/i-need-help-generating-a-formula-in-excel-2010?forum=excel


Scenario 3: How to count the cells based on the Conditional Formatting

Description: 
Sometimes, we need to count colors in cells that originate from conditional formatting. 

Solution:
Method 1:

If you use formula as rule in Conditional Formatting, we may use "Countif" formula to account the result.

•     Dim Cel As Range
•     Dim i%
•     For Each Cel In Sheets(1).Range("A1:A5")
•     If Cel.DisplayFormat.Interior.Color = "255" Then
•     i = i + 1
•     Cel.Select
•     End If
•     Next Cel
•     Range("B1") = i
• End Sub
• Sub Test1()
•      Dim     rge As Range, rgeUnion As Range, Cel As Range
•      Dim i%
•      
•      Set     rge = Range("A1").CurrentRegion
•      For Each Cel In Sheets(1).Range("A1:A5")
•              If Cel.DisplayFormat.Interior.Color = "255" Then
•                  If rgeUnion Is Nothing Then
•                      Set rgeUnion = Cel
•                  Else
•                      Set rgeUnion = Union(rgeUnion, Cel)
•                  End If
•              End If
•          Next
•      rgeUnion.Select

                                                                                                               

 End Sub
           

Method 2:

Use macro to count the color of the cells background:

URL:

http://social.technet.microsoft.com/Forums/en-us/7e9cfb0c-9fb7-4c73-9fad-6bff6277b196/excel?forum=officesystemzhchs

Summary

Conditional formatting helps you visually answer specific questions about your data. You can apply conditional formatting to a cell range, a Microsoft Excel table, or a PivotTable report. There are important differences to understand when you use conditional formatting on a PivotTable r