Solved

INDIRECT with a table row

Posted on 2011-09-19
7
285 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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