Issues in conditional formatting

[Sarfaraz]

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]

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]

Dear p45cal, Thanks a lot it works fine, thanks again. Kind Regards