Copying Condtional Formatting

[TMILJVIPM]

Guys,

I got a problem that I need some help with. On several of my sheets I use condtional formatting and I just realized that copying the cell with the condtional fromatting doesnt work like it does with the formula. Lets say cell K10 has the formula and the conditonal formatting in it. Well if I copy the cell and paste it from K11:K250 the formula automatically moves with each cell like it should but the conditional formatting is still linked to cell K10. Is there a way to copy the conditional formatting and paste it in all the cells and make it apply to that specific cell?

 

[VoG]

I think that there is a bug in Excel 2007 where it shows the CF for each cell as referring to the first row in the range. However, the CF is applied correctly as referring to the actual row. Could that be the case here?

 

[Aladin Akyurek]

anyone else got any ideas?

What do you have in the souce cell? And what do you want to happen at the destination?

 

[TMILJVIPM]

What do you have in the souce cell? And what do you want to happen at the destination?

Cell K10 formula is:
=if(I10="","",I10-J10)
Conditional format in Cell K10 is
Green Icon - >= $I$10*.2
Yellow Icon - > 0
Red Icon - <=0

I want this in K10:K256 along with other columns on the sheet. Over 1000 cells so doing it in each cell is impossible.

If excel cant do this I have managed to copy to other cells when using 3 color sets on using "formula to determine which cells to format" but I cant get the formula to work the same with the icon sets.

Basically the same:
when K10 is > 20% of I10 then cell green
when K10 is =< 20% of I10 then cell yellow
and of course over then red

 

[TMILJVIPM]

I think that there is a bug in Excel 2007 where it shows the CF for each cell as referring to the first row in the range. However, the CF is applied correctly as referring to the actual row. Could that be the case here?

Im not sure that I understand what you mean

 

[Norie]

I was going to suggest the simple alternative of not using this type of formatting, or using code to apply the correct formatting to each cell.

If you can confirm what cells you want to apply the conditional formatting to and what the formula is I can post something for the latter.

 

[Norie]

I've just read your post with the conditional formatting rules and they appear to change.

For the Yellow Icon you say it's >0 but then you say it's =< 20% of I10.

For the Red Icon you say it's <=0 then it's 'over'.

Maybe it's me but that's a bit confusing.

 

[Norie]

This is based on the first set of rules you posted:

Option Explicit
 
Sub DoTheFormat()
Dim rng As Range
 
    Set rng = Range("K10")
 
    While rng.Value <> ""
 
        With rng
            .FormatConditions.Delete
            .FormatConditions.AddIconSetCondition
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1)
                .ReverseOrder = False
                .ShowIconOnly = False
                .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
            End With
 
            With .FormatConditions(1).IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = "=" & rng.Offset(, -2).Address & "*.2"
                .Operator = 7
            End With

            With .FormatConditions(1).IconCriteria(2)
                .Type = xlConditionValueNumber
                .Value = 0
                .Operator = 5
            End With
        End With
 
        Set rng = rng.Offset(1)
 
    Wend
 
End Sub

PS I'm sure there's a chance of a formula solution or at least some sort of 'workaround'.

 

[TMILJVIPM]

This is based on the first set of rules you posted:

Option Explicit
 
Sub DoTheFormat()
Dim rng As Range
 
    Set rng = Range("K10")
 
    While rng.Value <> ""
 
        With rng
            .FormatConditions.Delete
            .FormatConditions.AddIconSetCondition
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1)
                .ReverseOrder = False
                .ShowIconOnly = False
                .IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
            End With
 
            With .FormatConditions(1).IconCriteria(3)
                .Type = xlConditionValueFormula
                .Value = "=" & rng.Offset(, -2).Address & "*.2"
                .Operator = 7
            End With
 
            With .FormatConditions(1).IconCriteria(2)
                .Type = xlConditionValueNumber
                .Value = 0
                .Operator = 5
            End With
        End With
 
        Set rng = rng.Offset(1)
 
    Wend
 
End Sub

PS I'm sure there's a chance of a formula solution or at least some sort of 'workaround'.

The range on the first column is K10:K256, also im inexperienced with running a code.

 

[Norie]

Sorry I don't understand what you mean about the range.

The code I posted will apply the conditional formatting from K19 down to the last row in that column that isn't empty.

So if there are formulas in K10:K256 then all cells will get the conditional formatting.

If you want to run the code:

1 Open the VBE (ALT+F11).

2 Goto Insert>Module

3 Paste the code into the new module.

4 Run the code:

a) Press F5 while still in the VBE.

b) Return to Excel (ALT+Q) and then on the Developer tab click Macros,
select DoTheFormat and click Run.

I don't know if applying this type of formatting to a range like you want to is possible without code.

Hopefully I'm wrong and somebody will post an alternative, perhaps formula-based, solution.

 

[TMILJVIPM]

Sorry I don't understand what you mean about the range.

The code I posted will apply the conditional formatting from K19 down to the last row in that column that isn't empty.

So if there are formulas in K10:K256 then all cells will get the conditional formatting.

If you want to run the code:

1 Open the VBE (ALT+F11).

2 Goto Insert>Module

3 Paste the code into the new module.

4 Run the code:

a) Press F5 while still in the VBE.

b) Return to Excel (ALT+Q) and then on the Developer tab click Macros,
select DoTheFormat and click Run.

I don't know if applying this type of formatting to a range like you want to is possible without code.

Hopefully I'm wrong and somebody will post an alternative, perhaps formula-based, solution.

Thanks for all your help! It did work in cell K10 but I need it to work in cells K10 thru K256 and then:
H10 Thru H256
N10 thru N256
Q10 thru Q256
T10 thru T256
W10 Thru W256
AA10 thru AA256