Conditional formatting question

[percy4]

Hi all,

My worksheet (mastersheet.xls) contains a database query (ODBC) and some links to another sheets. I attached a copy so it becomes more understandable.

I am an absolute beginner in VBA programming so maybe the “worksheet_change” event isn’t the way to go when trying to get VBA doing some conditional formatting for you?! The reason for not using the standard excel conditionalformatting is that I need more then three rules.
My format macro should format cells in a given column based on cell value. All values in these cells are calculated via a formula containing the TODAY()-function. The problem I am having is that when my querytable changes the conditional formatting doesen't change with it.




Thanks for any input I can get.

Have a nice day!

Best regards
Per

[mdmackillop]

What are you changing? This

Set Rng1 = Intersect(Range("E2:E20"), Target)

monitors the original change cell(s), not all those affected by the change. You can monitor say Column C and retain the code to maked the format changes in E. Add a break point at the start of the code and step through it.

[percy4]

What are you changing? This

Set Rng1 = Intersect(Range("E2:E20"), Target)

monitors the original change cell(s), not all those affected by the change. You can monitor say Column C and retain the code to maked the format changes in E. Add a break point at the start of the code and step through it.


What is changing is actually the value in cell Range("E2:E20").

So you say that I can monitor another column or even a single cell? In that case I would like to monitor one cell containing a database query table. If this works, everytime I run refresh on that query that will trigger an "worksheet change" event?

I've been trying so hard to get this to work so hopefully we are on to something.

Best regards
Per

[Bob Phillips]

This works for me



Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("E2:E20"), Target) Is Nothing Then

With Target

Select Case .Value

Case vbNullString

.Interior.ColorIndex = xlNone
.Font.Bold = False
.Font.ColorIndex = 1
.Font.Name = "arial"

Case -3000 To 0

.Interior.ColorIndex = 3
.Font.Name = "arial narrow"
.Font.Bold = True
.Font.ColorIndex = 2

Case 1 To 14

.Interior.ColorIndex = 6
.Font.Name = "arial narrow"
.Font.Bold = True
.Font.ColorIndex = 1

Case 15 To 1000

.Interior.ColorIndex = 10
.Font.Name = "arial narrow"
.Font.Bold = True
.Font.ColorIndex = 2

Case Else

.Interior.ColorIndex = xlNone
.Font.Bold = False
.Font.Name = "arial narrow"
.Font.ColorIndex = 1
End Select
End With
End If
End Sub

[percy4]

Hi, I run below code to update my query. When I do I get the type mismatch error on my formatting code.

Thanks so much for your help!



Sub skarp()
Msgbox "Data will now be refreshed. It may take up to 60 seconds."
Dim ws As Worksheet
'Dim qt As QueryTable
Dim wb As Workbook
Dim sourcefile As String
sourcefile = "H:\My Documents\Masterlistan\2\historik v2.0 final test.xls"
Application.StatusBar = "Macro running, please wait....."
Application.ScreenUpdating = False
Set wb = Workbooks.Open(sourcefile)
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="test"
Next ws
ThisWorkbook.RefreshAll
Debug.Print ThisWorkbook.Name
'For Each qt In ws.QueryTables
' qt.BackgroundQuery = False
' qt.Refresh
' Next qt
'Next ws
DoEvents
With wb
.Save
.Close
End With
Set wb = Nothing
Msgbox "Data refreshed!"
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="test", userinterfaceonly:=True
'ws.EnableAutoFilter = "true"
'ws.EnableSelection = xlNoRestrictions
Next ws
ThisWorkbook.Sheets(1).Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub