Ref's changing when conditionally form'd cells copied down.

[milesUK]

Good morning (it was when I bagan this post ),

I have an Excel macro which includes the following snippet of code to apply conditional formatting to a cell populated with a formula also applied by the macro

With Range("$M3")
        .FormatConditions.Delete
        .FormatConditions.Add xlCellValue, xlEqual, "=$K3"
        .FormatConditions(1).Interior.ColorIndex = 7 'magenta
    End With

An ODBC link then refeshes the cells to the left of column M and is set to copy down the formulas (and formatting) in adjacent cells.

Ocasionally when the macro is run I get the correct cell reference in the conditional formatting of cell M3 (i.e. =K3) but usually I get references either to a few rows down (eg =$K4, or =$K7) or more often in the region of =K65527 The cells below M3 increment until 65536 (max row number) is reached then continue with references from 1 onwards. This problem is present before (as well as after) the ODBC refresh edit-(ie M3 has a conditional format applied but referencing the wrong cell).

I also used to get strange column references as well but cured that one by the inclusion of the absolute reference ($) for the column; can not do same for row ref as it will not copy down correctly.

Has anyone any ideas about this please? It's getting very frustrating and I guess I'm just missing something simple. edit-(Not so simple eh?)

 

[milesUK]

This one has shown its face again. The following code

    With Cells(2, 7) 'Range("G2")
   
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RIGHT(G2)=""F"""

        .FormatConditions(1).Font.ColorIndex = 2
        .FormatConditions(1).Interior.ColorIndex = 3

    End With

is putting the following condition in Conditional Formatting for cell G2 =RIGHT(K65461)="F" when I am expecting =RIGHT(G2)="F". My solution last time was to apply the formatting to each cell in turn and use absolute refs. I would prefer not follow that route this time if possible.

Can anyone shed any light on this please; have just spent best part of 40 minutes googleing MrExcel for help using NateO's searching tips.

 

[NateO]

Hello,

Well, at least you tried to search.

Weird... I can replicate that... But I'm not sure why... Wonder if it has anything to do with you omitting the optional, 2nd argument?

Either way, try the following:

Sub foobar()
With Range("G2").FormatConditions
    .Delete
    .Add Type:=xlExpression, _
        Formula1:="=RIGHT(G2,1)=""F"""
    .Item(1).Font.ColorIndex = 2
    .Item(1).Interior.ColorIndex = 3
End With
End Sub

 

[milesUK]

Nate, Thank you for trying this; yes it is wierd!

I had originally used the "G2" type reference but changed to Cells(2, 7) in an attempt to cure the issue.

Got rid of the problem by removing a superfluous ".Select" method in a loop above the conditional formatting formatting code; though I was stumped as to why this should have the effect.

But then the penny dropped & I realised that even with the use of "With Range("G2")" the conditional formatting was being applied to the last cell to be Activated/Selected. Thus:

With Range("G2").FormatConditions
 
    .Activate '***required to prevent cell reference errors in conditional formatting***

    .Delete 
    .Add Type:=xlExpression, Formula1:="=RIGHT(G2,1)=""F""" 
    .Item(1).Font.ColorIndex = 2 
    .Item(1).Interior.ColorIndex = 3 
End With

works fine every time. Don't know what you did but it helped me to solve a long standing headache. Problem shared and all that.. ..cheers.


Just for info my actual code is included below.

Option Explicit

Sub Excel_Gantt()
'Create a Gannt chart in Excel from an Access output of an outage listing.
'Sheet Columnheadings are: Start, End, Circuit, Work, Voltage, Status.
'14/03/2006 by Miles
Dim c As Range
Dim rng As Range, source_rng As Range, fill_rng As Range
Dim circuit_kv As String, outage_status As String, outage_work As String
Dim last_row As Long

'find the last row
    last_row = Range("C1").End(xlDown).Row
    Set rng = Range("C1:C" & last_row)

'sort
    Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, _
               Key2:=Range("B2"), Order2:=xlAscending, _
               Key3:=Range("C2"), Order3:=xlAscending, _
               Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'clear existing comments
    rng.ClearComments

'copy voltage, status and work into a comment for each outage
    For Each c In rng
        c.AddComment
            circuit_kv = c.Offset(0, 2).Text & "kV, "
            outage_status = c.Offset(0, 3).Text & ", "
            outage_work = c.Offset(0, 1).Text
        c.Comment.Text Text:=Left(circuit_kv & outage_status & outage_work, 255) 'comment can only accept 255 chars
    Next c

'create Gantt Chart
'==================
'insert date heading
    Range("G1:IV" & last_row).Clear
    Range("G1").Value = Int(Now())
    Range("H1").Formula = "=G1+1"
    Range("H1:IV1").FillRight
    Range("G1:IV1").NumberFormat = "dd"

'insert formulae
    With Range("G2")
        .Activate 'required to prevent cell reference errors in conditional formatting
        .Formula = "=IF(G$1>=$A2,IF(G$1<=$B2,left($E2) & left($F2),""""),"""")"
    
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RIGHT(G2)=""F"""
        .FormatConditions.Add Type:=xlExpression, Formula1:="=RIGHT(G2)=""A"""
        .FormatConditions.Add Type:=xlExpression, Formula1:="=G2<>"""""
        .FormatConditions(1).Font.ColorIndex = 2
        .FormatConditions(1).Interior.ColorIndex = 3
        .FormatConditions(2).Font.ColorIndex = 2
        .FormatConditions(2).Interior.ColorIndex = 41
        .FormatConditions(3).Font.ColorIndex = 2
        .FormatConditions(3).Interior.ColorIndex = 43
    End With

'copy G2 to complete the Gantt chart
    Set source_rng = Range("G2")
    Set fill_rng = Range("G2:IV2")
    source_rng.AutoFill Destination:=fill_rng
    
    Set source_rng = fill_rng
    Set fill_rng = Range("G2:IV" & last_row)
    source_rng.AutoFill Destination:=fill_rng

'calculate
    Cells.Calculate

'format
    Columns("A:IV").EntireColumn.AutoFit
    Columns("D").Hidden = True ' Shift:=xlShiftToLeft
    Columns("G:IV").ColumnWidth = 2
    Range("G2").Select

End Sub

 

[NateO]

Well, I changed the With Statement construct and the Right() call and it works as expected, without selecting/activating anything...

So, something you might want to consider...

 

[milesUK]

Something weird is definitely going on here, Nate.

I carried out the following tests:

Select cell G2 and run this code:

Sub foobar() 
    With Range("G2").FormatConditions 
        .Delete 
        .Add Type:=xlExpression, Formula1:="=G2>10" 
        .Item(1).Font.ColorIndex = 3 'red
    End With 
End Sub

and the condition formula =G2>10 is applied to cell G2 as expected. Select cell G3 and run the code and the condition =G1>10 is applied to cell G2.

This offset from the active cell to the opposite side of the cell to be CF'd seems to be consistant.

Select I4 and Excel tries to apply the condition formula =E0>10 but can't so inserts =E65536>10 instead. This now explains why the odd references were being produced. It is as if Excel applies the condional formatting to the active cell then moves it to the target cell changing the relative cell ref's as it does so. I must Select or Activate G2 before applying CF.

I've reproduced this on Excel97(win95) and on Excel2000 (9.0.7616 SP-3) on WinXP.