PDA

View Full Version : Multiple/Dynamic Conditional Formating



hyperdreamz
12-03-2006, 06:31 PM
.



Hi All

I need assistance to write a macro to conditionally format the attached sheet

The sheet deals with Issues and Ticket Age for a given issue

In column “B” contains the numerical value of the TOTAL number of hours this ticket has been open

In column “C” contains the numerical value of the TOTAL number of hours since this ticket was last updated

On “Sheet 2” is a MASTER Index of Maximum response time for a particular issue (Changes daily)


I want to use multiple conditional formats, based on MASTER Index on “Sheet 2”

E.g.

The Maximum allowed age of a ticket for a "Os Issue" is 24Hrs (=Sheet2!B2)

If the "Ticket Age" for an "Os Issue” exceeds 24 I want row’s A:C to show in a Red font

If the ("Ticket Age" - "Modified Age") for an "Os Issue" exceeds 24 I want row's A:C to show in a White font with a Red background

I want the formatting to update its self if changes are made in the “MASTER Index”

I don’t need the formatting to update in real-time, if it is updated every time the macro is run that’s fine

Is this possible ? :banghead:


-HyperDreamz


.

Bob Phillips
12-04-2006, 03:21 AM
Public Sub Test()
With Worksheets("Sheet2")
.Range("A1").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row, 2).Name = "lookupTable"
End With

With Worksheets("Sheet1")
With .Range("A2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 1, 3)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$C2>VLOOKUP($A2,lookupTable,2,False)"
With .FormatConditions(1)
.Font.ColorIndex = 1
.Interior.ColorIndex = 3
End With
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$B2>VLOOKUP($A2,lookupTable,2,False)"
.FormatConditions(2).Font.ColorIndex = 3
End With
End With
End Sub

hyperdreamz
12-04-2006, 04:52 AM
.


:dunno

Doesnt seem to be working


- HyperDreamz


.

Bob Phillips
12-04-2006, 05:03 AM
Did for me.

What doesn't work?

hyperdreamz
12-04-2006, 05:13 AM
.

Inserted the script in a module on the Same Sample Workbook and ran it

No go

Is there anything special i need to do ?

- HyperDreamz

.

Bob Phillips
12-04-2006, 05:23 AM
Sounds right

Charlize
12-04-2006, 06:11 AM
I needed to change a couple of things in the formules.

1. vlookup isn't a function in dutch = vert.zoeken
2. false = onwaar
3. needed to replace , with ;

Because I'm using a dutch version and not an english one.

Charlize

hyperdreamz
12-04-2006, 09:46 AM
Sounds right


My bad

Had some issues with my Personal.xls

All good works like a charm

I thank you for the prompt and comprehensive assistance


- Hyperdreamz