PDA

View Full Version : [SOLVED] Issues in conditional formatting



Sarfaraz
10-01-2013, 04:58 AM
Hi,
i am having an issue with conditional formatting, in the attached sheet yellow cells have targets. I have applied conditional formatting on cell E9. When I use format painter to apply same format for other cells in green it doesn't work correctly. Because of locked cell in rule i.e. $E$8. is there a way by which I can use format painter in all green cells and rule cell reference changed automatically for all green cell i.e. cell above that cell.

p45cal
10-02-2013, 06:58 AM
This can be a right pain..
For your specific sheet, run this macro (while that sheet is the active sheet) which puts yor conditional formatting in each cell one at a time:
Sub blah()
For i = 5 To 105 Step 50 'left columns of regions
For r = 9 To 30 Step 3 'row numbers for that year
For c = i To i + 44 Step 4 'column numbers for that year
Set cll = Cells(r, c)
With cll
.FormatConditions.Delete
.FormatConditions.AddIconSetCondition
.FormatConditions(cll.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
End With
With .FormatConditions(1).IconCriteria(2)
.Type = xlConditionValueFormula
.Value = "=" & cll.Offset(-1).Address & "*0.8"
.Operator = 7
End With
With .FormatConditions(1).IconCriteria(3)
.Type = xlConditionValueFormula
.Value = "=" & cll.Offset(-1).Address
.Operator = 7
End With
End With
Next c
Next r
Next i
End Sub


For a more general situation, the code below puts the same conditional format in each of the selected cells and bases it on the cell above (in your case your cells are not contiguous, so to apply conditional formatting to several cells at once you should hold down the control key while selecting the individual cells you want set the conditional format for):
Sub blah2()
For Each cll In Selection.Cells
With cll
.FormatConditions.Delete
.FormatConditions.AddIconSetCondition
.FormatConditions(cll.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3Symbols2)
End With
With .FormatConditions(1).IconCriteria(2)
.Type = xlConditionValueFormula
.Value = "=" & cll.Offset(-1).Address & "*0.8"
.Operator = 7
End With
With .FormatConditions(1).IconCriteria(3)
.Type = xlConditionValueFormula
.Value = "=" & cll.Offset(-1).Address
.Operator = 7
End With
End With
Next cll
End Sub
In both codes, any existing conditional formatting gets removed before adding any formatting.

Sarfaraz
10-03-2013, 12:01 AM
Dear p45cal, Thanks a lot it works fine, thanks again. Kind Regards