Improve company productivity with a Business Account.Sign Up

x
?
Solved

INDIRECT with a table row

Posted on 2011-09-19
7
Medium Priority
?
359 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
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
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

595 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