How to Get Highlighting to Work on ONLY the Active Sheet, Yet Still Have Any Formula Calculations Occur?

[Fuddy_Duddy]

When I have multiple workbooks open and have highlighting using conditional formatting and VBA, all of the sheets get highlighted on the same respective row.

Is there a way to have the highlighting only occur on the active workbook sheet yet still have all of the workbooks updated if a change is made in the active workbook sheet?

The reason for using highlighting is useless if all of the open workbooks get highlighted in the same row every time a cell is clicked.

Any help will be greatly appreciated.

 

[jkpieterse]

What does your highlighting code look like? (I mean, the formula in the CF and the VBA code triggering the highlight to change)

 

[Fuddy_Duddy]

Thanks for responding.

Here's the Worksheet_SelectionChange:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.Calculate

End Sub

And the Conditional Formatting:

=ROW()=CELL("row")

As I said before, they work correctly, but if I have more than one workbook open that has this, they all are synced together and highlight the same rows.

 

[MountainFog]

The reason why this is happening is because CELL(info_type, [reference]) If reference is omitted, then is calculate based off whichever cell is selected at the time of calculation.

I can think of two POTENTIAL ways to get around this.

1) Open up each workbook in a separate instance of Excel. (Hold down ALT while opening excel)

2) Where is your Macro pasted? Is it in a module? Or worksheet? Or what?

 

[Fuddy_Duddy]

The reason why this is happening is because CELL(info_type, [reference]) If reference is omitted, then is calculate based off whichever cell is selected at the time of calculation.

I can think of two POTENTIAL ways to get around this.

1) Open up each workbook in a separate instance of Excel. (Hold down ALT while opening excel)

2) Where is your Macro pasted? Is it in a module? Or worksheet? Or what?

The macro is in the Worksheet_SelectionChange. The code I posted above is exactly the way every instructional regarding highlighting rows is presented online. The coding works...except in works in every open workbook that has this coding. When I activate a row on one workbook, all of the others jump to the same corresponding row.

 

[jkpieterse]

I have an alternative addin, but it clears the undo stack: Dozens of Free Excel Downloads For You