Refresh external data looses conditional formatting

kmcaron

New Member
Joined
Aug 19, 2015
Messages
8
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi all,
I use the Data > Refresh button on several spread sheets. On a recent one, I added a balance column adjacent to the external data that references cells in the external data to highlight a cell in the balance column. This works great until I Refresh the data, then the conditional formatting is lost. The following VBA code will reestablish the conditional formatting:

VBA Code:
Sub HideInvoice()
  ActiveSheet.Range("j8:j1000").FormatConditions.Delete
  With ActiveSheet.Range("j8:j1000").FormatConditions.Add( _
    Type:=xlExpression, _
    Formula1:="=$D8>2")
    .Font.Color = RGB(255, 255, 255)
  End With
End Sub

The last piece of the puzzle to solve is, how can I tell the external data was refreshed so this subroutine can be called?

advTHANKSance,
Ken
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
There is the possibility for creating the event "Refresh completed", but I suggest that you insert this new macro:
VBA Code:
Sub MyRefresh()
'
    ActiveWorkbook.RefreshAll
    Call HideInvoice
End Sub

Then, instead of using the native command RefreshAll you start this Sub MyRefresh; you can connect the macro to a button on the worksheet to simplify the process.

HOWEVER, if the Queries that you refresh have their BackgroundQuery property set to True then the command Call HideInvoice will likely be excecuted before the query be completed, and this will frustrate the operation.
So you should check that the abovementioned property be set to False

But there is another option that probably will avoid losing the formatting: check the External Data property box, and make sure that the option "Keep cell formatting" (or something similar) is checked.

Bye
 
Upvote 0
Thanks Anthony47. Your post got me to thinking about using the Worksheet_Change event. since this data is external I will not manually be manipulating it. I created the Worksheet_Change subroutine to watch for the first cell in the external data range to change, thus someone probably refreshed it. Here is my final solution.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Row = 7 And Target.Column = 1) Then ' A7 = first cell of external data range
      ActiveSheet.Range("j8:j1000").FormatConditions.Delete
      With ActiveSheet.Range("j8:j1000").FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=$D8>2")
        .Font.Color = RGB(255, 255, 255)
      End With
    End If
End Sub

I guess I could be more general by using xlEnd to get the last cell in the J column, but so far this is good enough.

Ken
 
Upvote 0
Good to know that you got a solution

Did you try using Query properties? No luck?

Bye
 
Upvote 0
I am using the "Get External Data From Text" file. The Connection Properties, usage tab, doesn't appear to have the option to "Keep cell formatting". The files I am importing are csv files from credit card and bank account.

Ken
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top