Dont fix cell colour if cell has no Interior colour or Conditional Formatting

[julhs]

The snippet of code below converts the applied Condition Formatting formatting rule to a FIXED format so that when
the Sub continues and the cell is subsequently copied it ONLY copies the format not the CF rule.
Slight issue with it is that it’s filling cells that have NO formatting what so ever to “White” Interior.Color which of course gets copied.
I have tried to handle it by adding an "IF" to the snippet to test for formatting; if it has its Fixed , if it doesn’t it skips it.
But I just couldn’t get the syntax right, can anyone help me out?

For Each r In UsedRng
     r.Interior.Color = r.DisplayFormat.Interior.Color   ' This converts the Conditional Formatting effect to a "Fixed" static color format
     r.Font.Color = r.DisplayFormat.Font.Color   ' This keeps any Font color as it was, without it the font will be black
Next r
     UsedRng.FormatConditions.Delete   ' This deletes the CF rule from the range just converted to "Fixed" colors BEFORE range is copied

 

[Joe4]

Do you mean something like this?

For Each r In UsedRng
    clr = r.DisplayFormat.Interior.Color   'get current interior color of cell
    If clr <> 16777215 Then    'only proceed if color is not white
        r.Interior.Color = r.DisplayFormat.Interior.Color   ' This converts the Conditional Formatting effect to a "Fixed" static color format
        r.Font.Color = r.DisplayFormat.Font.Color   ' This keeps any Font color as it was, without it the font will be black
    End If
Next r

UsedRng.FormatConditions.Delete   ' This deletes the CF rule from the range just converted to "Fixed" colors BEFORE range is copied

 

[julhs]

Joe
Thanks for response; I had thought this query was going to go unanswered.
What you gave me is returning error "Next without For"

But to expand/clarify further to what it is I’m trying to achieve.
FIX all CF rule results (in range to be copied) to a static/fixed format so that when cells are copied, ONLY the fixed/static format is copied NOT the CF rule as well.
Reason being; IF the CF rule is copied to the new location, ALL the CF rule references get screwed up and the copied cells result in inaccurate formatting (in the new location).
My dilemma is that any cell WITHOUT any applied CF rule (so no format) is being turned to “White” interior fill by my existing code and the subsequent COPIED cell is resulting in “White” inertia fill colour, as opposed to "No fill"

 

[julhs]

Update
Firstly, I negected to declared a new variable for “clr”, so declared it as “Integer” is that correct?
When I add the “End If” as you placed it, it resulted in “Run-time error ‘6’ Overflow

 

[Joe4]

Update
Firstly, I negected to declared a new variable for “clr”, so declared it as “Integer” is that correct?
When I add the “End If” as you placed it, it resulted in “Run-time error ‘6’ Overflow

"Integer" is not big enough to hold those numbers. Try using "Long" instead.

 

[julhs]

At least changing “Integer” to “Long” prevented the Error message.
But going to have to look more in-depth into my existing code as it appears I’m copying the CF formulas.
Will investigate and come back to you.
Julhs

 

[julhs]

With what you gave me and sorting my school boy error regarding the range reference to be copied, things worked very much as I wanted.
The 0.1% that doesn’t seem to be working is the CF rule applies a cell pattern as well as a colour, when fixing format it is losing the applied cell pattern.
It is such a minor detail, if it’s not easily rectifiable then I can live with it.

 

[Joe4]

The 0.1% that doesn’t seem to be working is the CF rule applies a cell pattern as well as a colour, when fixing format it is losing the applied cell pattern.

What is the exact CF rule in that particular case?
What exactly is happening, and what would you like to happen in that case?

 

[julhs]

Hope you can work with the below as can’t quickly figure out how to upload the CF rule itself
Formula: =$R9="To Arran", Applies to =$O$9:$O$440 and resulting formatting is set to, Yellow fill with poker dot cell pattern.
When FIXING the formatting, it fixes the cell colour but seems to be losing the poker dot cell pattern aspect

 

[Joe4]

Try this:

For Each r In UsedRng
    clr = r.DisplayFormat.Interior.Color   'get current interior color of cell
    If clr <> 16777215 Then    'only proceed if color is not white
        r.Interior.Color = r.DisplayFormat.Interior.Color   ' This converts the Conditional Formatting effect to a "Fixed" static color format
        r.Interior.Pattern = r.DisplayFormat.Interior.Pattern
        r.Interior.PatternColorIndex = r.DisplayFormat.Interior.PatternColorIndex
        r.Font.Color = r.DisplayFormat.Font.Color   ' This keeps any Font color as it was, without it the font will be black
    End If
Next r

UsedRng.FormatConditions.Delete

Note that the two new lines of code I added are the ones in red.