Solved

INDIRECT with a table row

Posted on 2011-09-19
7
270 Views
Last Modified: 2012-05-12
Greetings,

I am creating a conditional formatting formula in VBA. I have the following formula working just fine:

=INDIRECT(""D""&ROW())=""Employee""

In this case, the entire row is shaded when column D and the current row is equal to Employee.

I now want to make this work on a bunch of different data tables where the Status Column = Mapped. I am struggling on how to make this formula dynamic enough to reference the row in the data table where the value of Status = Mapped. I am at a loss at the moment on how to do that most efficient.

I see the formula looking something like =indirect( <the Status column> & ROW()) = "Mapped". I do not know how to properly address <the Status column>

Any tips would be appreciated.
0
Comment
Question by:ckelsoe
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36564042
Hello,

why are you using Indirect at all?

If you want to format a cell in the same row, then just enter the relative reference for the row, for example, if the selected cell is in row 2 when you define the conditional format, use

=$D2="Employee"

As for the Status column: does the position of the status column differ in the sheets? Do you need to identify which column holds the status values?

This would be a lot easier to understand if you could post a sample file. Make sure to replace confidential data with dummy text.

cheers, teylyn
0
 

Author Comment

by:ckelsoe
ID: 36564189
I am need to do this as conditional formatting as the tables are imported data with unknown size and column locations. conditional formatting is a vba free way of achieving the required results.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36564219
Yes, but why the Indirect?

See the attached file. Sheet1 has two conditional formatting rules.

blue for "employee" in column D
=$D2="employee"

gold for "mapped" in the column that has "Status" as the column header
=INDEX($A2:$F2,MATCH("Status",$A$1:$F$1,0))="mapped"

Sheet2 has some data as well, but the Status column is not in column F. Copy Sheet1, cell A2, then select the table in Sheet2 and use Paste Special > Formats to paste only the formatting.

The correct rows will be highlighted.

Is that something you could work with?

cheers, teylyn
Book6.xlsx
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:ckelsoe
ID: 36564677
That should work. I will play around with it.

I went with Indirect because of this information: http://www.asap-utilities.com/blog/index.php/2005/11/07/conditional-row-color-based-on-a-cell-value/

I just need to get the resulting condition applied from a VBA module that is able to work no matter what size the table is and no matter what column the column Status is located at.
0
 

Accepted Solution

by:
ckelsoe earned 0 total points
ID: 36581276
This is the code that I used to solve this problem that works well.


Range("TheRange").Select
                Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
                  "=INDIRECT(""D""&ROW())=""Employee"""
                Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                With Selection.FormatConditions(1).Interior
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent3
                    .TintAndShade = 0.399945066682943
                End With
                Selection.FormatConditions(1).StopIfTrue = False

Open in new window

0
 

Author Closing Comment

by:ckelsoe
ID: 36708095
This solution achieved the desired results
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36708419
I know this question is closed.

For posterity:

Indirect is not required to address a cell in the same row

=INDIRECT("D"&ROW())="Employee"

This formula is overly complicated. Depending on my mood, I'd call it anywhere between "amateur" and "rubbish". It works. That's indisputable. But a much cleaner way of achieving the same result is

=$D2="Employee"

when the formula is applied in row 2. When using conditional formats, relative addresses are relative (surprise!) to the current cell. Copied to another row, that formula will adjust to the relevant row, without the overhead of an overly complicated INDIRECT() with a nested ROW(). Two additional calculations that Excel has to perform per row.

If you want fast performing worksheets, keep the overhead to a minimum.

cheers, teylyn
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question