Solved

INDIRECT with a table row

Posted on 2011-09-19
7
276 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
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.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

839 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