Excel formatting syntax won't accept dynamic cell location 1

[FiniteMonkeys]

I'm trying to modify a bit of Excel code to work with dynamically referenced locations, and I'm stumped.

Here is the working code with non-dynamic referencing. You start by selecting a range of cells (say A1:C3), then run this code to conditionally bold some of the cells in the selected range based on the values found in an a second different range of cells (in this case, the equivelently shaped 3 by 3 block of cells starting with E1 at the top right corner). Here is the working code:


Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E1<0.05"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.TintAndShade = 0
End With



The problem is that I would like to dynamically assign the cell address in this code using a value that I calculate elsewhere. But if I start rewriting this as:

"MyLocation = E2

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MyLocation <0.05"
...
"

It interprets "MyLocation" as a literal value, not as a variable to interpolate. I've tried messing with adding more quote marks, but I can't make it work.

Help?

Alex

 

[macropod]

Hi Alex,

How you implement this depends on whether you're passing 'MyLocation' as a Range or as a String.

For a range:

Sub Demo()
Dim MyLocation As Range
Set MyLocation = ActiveSheet.Range("E2")
With Selection
  .FormatConditions.Add Type:=xlExpression, Formula1:="=" & MyLocation.Address & "<0.05"
  .FormatConditions(.FormatConditions.Count).SetFirstPriority
  With .FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .TintAndShade = 0
  End With
End With
End Sub

For a string:

Sub Demo()
Dim MyLocation As String
MyLocation = "E2"
With Selection
  .FormatConditions.Add Type:=xlExpression, Formula1:="=" & MyLocation & "<0.05"
  .FormatConditions(.FormatConditions.Count).SetFirstPriority
  With .FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .TintAndShade = 0
  End With
End With
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[FiniteMonkeys]

Thanks so much, this was VERY helpful.

Minor correction: I found when debugging the code that if you define it as a range you have to insert:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" & _
MyLocation.Address(RowAbsolute:=False, ColumnAbsolute:=False) _
& "<0.05"

Otherwise it inserts the cell value as "$E$2" instead of "E2".

 

[SkipVought]

Your original post refers to the SYNTAX of your posted code.

The posted solution by macropod, relating to the address property of the range variable, MyLocateon, was absolutely correct.

Your 'minor correction' is a LOGIC correction, NOT a SYNTAX 'correction'.

You used the great information provided by macropod to learn something, using that and build a better mouse trap.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FiniteMonkeys]

Sorry, I didn't mean that as a knock on the original advise at ALL! You are exactly right that it was a vital and absolutely accurate account of how to carry out the syntactic function I was struggling with. I am most grateful!

I was just trying to share, above and beyond that, the extra tweak it took to get the code to completely fulfill the original stated purpose (i.e., to conditionally format one stretch of cells based on the values in a second same-shaped stretch). I probably shouldn't have called it a "correction", but without that tweak it would format the selected range of cells based on the value of only ONE other cell, not on the matching RANGE of cells. Nobody else may ever care about this problem, but if they do, I thought it was better to share a more complete description of how to solve it up on the thread.

I'm very grateful to macropod for their post, and starred it with alacrity!

 

[macropod]

Hi Alex,

Re:

I found when debugging the code that if you define it as a range you have to insert:

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" & _
            MyLocation.Address(RowAbsolute:=False, ColumnAbsolute:=False) _
                                        & "<0.05"

Otherwise it inserts the cell value as "$E$2" instead of "E2".

True, but that would only matter if you're planning to copy the format and paste it to another location. Whether doing so is appropriate depends on whether you want to formula at the new location to refer back to the original address or to be offset whatever # of rows and/or columns it's offset. If you're only offsetting by row OR columns, you don't need to set both values to False. Equally, if this code were being executed as part of a loop in which the MyLocation range changes with each iteration, there would be no need to set either value to False.

Given that you apparently wanted a completely relative address, you could also have used:

Selection.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=" & Replace(MyLocation.Address,"$","") & "<0.05"

Finally, I'd suggest you look at whether you really need to use Selections. Doing so makes for relatively inefficient code.

Cheers
Paul Edstein
[MS MVP - Word]

 

[FiniteMonkeys]

Thanks. That is a far more concise statement.

Thanks also for the advice about selecting being inefficient. If I were to build this code again I would define the block of cells as a range variable, and then specify manipulations based on that. This would run faster, would it? This was my first real vba program, so I've been fumbling my way through based on a lot of macro recording, and snips of code from online help sites!

FWIW, what I'm trying to do in this one is format grids of correlation coefficients to make them easier to read. SAS spits these out as one grid of "Pearsons R" correlation coefficients, and a second neighboring grid with the corresponding probability scores... This bit of my program bolds R scores when the corresponding P score indicates statistical significance (i.e., is less than .05). As such, I'm almost always working with square grids covering multiple rows and columns, and the only way to know which p cell matches which r cell is because they have the same relative position within identically sized grids.

Thanks for the invaluable advise!

 

[SkipVought]

Hi,

I have not followed you thread, but I have read you last post.

In most well designed workbook/worksheets, I look to define ONE cohesive formula when it makes sense. Grids within grids, create special challenges. It might be possible to use the INT() and MOD() spreadsheet functions to create one formula that could work anywhere in the grid at large and would not require VBA to define sub-grid areas. I'm not sure that you are approching the problem this way, so I just throw it out there.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FiniteMonkeys]

Sorry Skip, I don't really follow you here. How would you use int() to reliably select subsets of cells?

 

[Gruuuu]

Most likely in conjunction with offset or indirect.

 

[SkipVought]

Yes, Offset can be used to define a sub range.

If you have a pattern that repeats ever n, then INT(x)*n defines the first occurrence of each of these groups and x MOD n defines the occurrences within the group.

Keep in mind that the objective is to construct ONE formula that can correctly yield the correct value in any cell in the grid.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FiniteMonkeys]

so... trying to follow here... if your cells go

A
B
C
A
B
C
A
B
C

then your n=3

so you are suggesting you can loop through them with something like (in very crude pseudocode)

for x=1 to [some upper limit]
rangeVariable = INT(x)*n:MOD(n,x)
end if

I can't have the mod part of this right, it just returns a 1, a 2, and a 0.

 

[SkipVought]

I'm suggesting that you don't need ANY VBA.

You can do it with Spreadsheet Formulas.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Let me demonstrate with a a group of 3 rows starting at row 2

the Group ROW offset is
[tt]
=INT((ROW()-2)/3)
[/tt]
The Group interior index is
[tt]
=MOD(ROW()-2,3)
[/tt]
You can use those formulas for ALL the groups in your table. You may have some other formulas to construct of your table is more than one column, but the principle is identical.

Alternatively, you can use and reference a table3 row number and column number rather than ROW() or COLUMN()


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}