Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONDITIONAL FORMATTING FOR MORE THAN 3 CRITERIA
I had placed the following question arround 5 days back:
___ I have a data like a figure reflected in A1 required to be: 1. Cell colored Green if Branch Name in B1 is "BR1"; 2. Text to be bold if Region in C1 is "South"; 3. Cell to be bordered thick if Segment in D1 is "Auto"; and 4. Text to be colored Red if Rate in E1 is 15%; The problem is that a case may also be belonging to BR1 in SOUTH for AUTO with RATE 15% thus the cell being: Colored Green with Bold and Red Text Bordered Thickly; All means macros codes shall be highly obliged!!! ___ Upon such a question, J. Sperry replied in a great manner as: ___ Sub FunkyFormat() Range("a1").Select If ActiveCell.Offset(0, 1) = "BR1" Then With Selection.Interior .ColorIndex = 10 .Pattern = xlSolid End With End If If ActiveCell.Offset(0, 2) = "South" Then Selection.Font.Bold = True If ActiveCell.Offset(0, 3) = "Auto" Then Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With End If If ActiveCell.Offset(0, 4) 0.15 Then Selection.Font.ColorIndex = 3 End Sub ___ The problem is that such a code is not working as a conditional one, as upon removal of the criterias the original cell does not revert back to the original format; AND What if I need to apply such a conditional formatting on any active cell instead of only Cell A1? All help shall be highly obliged. Thanx pals!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CONDITIONAL FORMATTING FOR MORE THAN 3 CRITERIA
Bob Phillips has a free add-in that you can download from he
http://www.xldynamic.com/source/xld.....Download.html It gives you 30 conditional formats in each cell. Hope this helps. Pete On Feb 12, 4:36*am, FARAZ QURESHI wrote: I had placed the following question arround 5 days back: ___ I have a data like a figure reflected in A1 required to be: 1. Cell colored Green if Branch Name in B1 is "BR1"; 2. Text to be bold if Region in C1 is "South"; 3. Cell to be bordered thick if Segment in D1 is "Auto"; and 4. Text to be colored Red if Rate in E1 is 15%; The problem is that a case may also be belonging to BR1 in SOUTH for AUTO with RATE 15% thus the cell being: Colored Green with Bold and Red Text Bordered Thickly; All means macros codes shall be highly obliged!!! ___ Upon such a question, J. Sperry replied in a great manner as: ___ Sub FunkyFormat() Range("a1").Select If ActiveCell.Offset(0, 1) = "BR1" Then * * With Selection.Interior * * * * .ColorIndex = 10 * * * * .Pattern = xlSolid * * End With End If If ActiveCell.Offset(0, 2) = "South" Then Selection.Font.Bold = True If ActiveCell.Offset(0, 3) = "Auto" Then * * Selection.Borders(xlDiagonalDown).LineStyle = xlNone * * Selection.Borders(xlDiagonalUp).LineStyle = xlNone * * With Selection.Borders(xlEdgeLeft) * * * * .LineStyle = xlContinuous * * * * .Weight = xlMedium * * * * .ColorIndex = xlAutomatic * * End With * * With Selection.Borders(xlEdgeTop) * * * * .LineStyle = xlContinuous * * * * .Weight = xlMedium * * * * .ColorIndex = xlAutomatic * * End With * * With Selection.Borders(xlEdgeBottom) * * * * .LineStyle = xlContinuous * * * * .Weight = xlMedium * * * * .ColorIndex = xlAutomatic * * End With * * With Selection.Borders(xlEdgeRight) * * * * .LineStyle = xlContinuous * * * * .Weight = xlMedium * * * * .ColorIndex = xlAutomatic * * End With End If If ActiveCell.Offset(0, 4) 0.15 Then Selection.Font.ColorIndex = 3 End Sub ___ The problem is that such a code is not working as a conditional one, as upon removal of the criterias the original cell does not revert back to the original format; AND What if I need to apply such a conditional formatting on any active cell instead of only Cell A1? All help shall be highly obliged. Thanx pals!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONDITIONAL FORMATTING FOR MORE THAN 3 CRITERIA? | Excel Discussion (Misc queries) | |||
need help with conditional formatting 3 criteria | Excel Worksheet Functions | |||
Conditional Formatting with more than 3 Criteria | Excel Discussion (Misc queries) | |||
Multiple Criteria for Conditional Formatting | Excel Worksheet Functions | |||
Can I set more than three criteria for conditional formatting? | Excel Worksheet Functions |