@satya - Here's an example of why I love Microsoft. I forwarded your question to the internal Excel experts and got this response from Randy.....send me your email address if you want me to forward the Excel doc to you. Contact me via the blog contact page.
It can be done with code if he has lots of conditions.
I made a simple macro that selects 100 cells (A1:A100) and makes 100 Format Conditions, using Random Colors. (First Button)
The customer could make an Array of (Cell address, Conditional Values, Interior Color, and Font Color ), and run then in a macro, without having to do each manually. (Second button)
I tested it in XL2010 with up to 1000 cells and 1000 custom formats.
Option Explicit
Option Base 1
Sub TestArray()
Dim FormatArray()
FormatArray = Sheet2.Range(Cells(2, 8), Cells(6, 10)).Value
Debug.Print FormatArray(5, 2)
End Sub
Option Explicit
Sub ConditionalFormattingMacro()
Dim celFMT As FormatCondition
Dim x
Dim vRed, vGreen, vBlue
Application.ScreenUpdating = False
ActiveSheet.Range("A1:A100").Select
Selection.Clear
For x = 1 To 100
Randomize
vRed = Int(Rnd() * 255)
vGreen = Int(Rnd() * 255)
vBlue = Int(Rnd() * 255)
Set celFMT = Selection.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & x)
celFMT.Interior.Color = RGB(vRed, vGreen, vBlue)
Randomize
vRed = Int(Rnd() * 255)
vGreen = Int(Rnd() * 255)
vBlue = Int(Rnd() * 255)
celFMT.Font.Color = RGB(vRed, vGreen, vBlue)
Next
[A1].Select
End Sub
Sub ConditionFormatting_CustomArray()
Application.ScreenUpdating = False
Dim celFMT As FormatCondition
Dim x
Dim vRed, vGreen, vBlue
Dim FormatArray()
Sheet2.Select
FormatArray = Sheet2.Range(Cells(2, 8), Cells(6, 10)).Value
Sheet1.Select
ActiveSheet.Range("A1:A100").Select
Selection.Clear
'Setting 5 conditions for the format range.
For x = 1 To 5
Set celFMT = Selection.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & FormatArray(x, 1))
celFMT.Interior.ColorIndex = FormatArray(x, 2)
Randomize
vRed = Int(Rnd() * 255)
vGreen = Int(Rnd() * 255)
vBlue = Int(Rnd() * 255)
celFMT.Font.ColorIndex = FormatArray(x, 3)
Next
End Sub