Conditional Formatting (5 conditions) 2

[Jock1970]

This seems to be quite a common question on here and before anyone jumps in with a 'read the previous posts' remark, yes - I have already. Some of the answers given previously are way over my head, but I don't think anyone who asked before has been trying to do the same as me (format a range of cells based on the value in another cell)

I have a workbook with several worksheets which are all summarised in the front worksheet. One of the columns in the front worksheet is a lookup from the other sheets and has text in it (5 different standard texts describing a jobs current progress). The column with this data (J) is hidden so that it does not show on printouts but I want the jobs (in cells B to G) to have a fill colour dependant upon the text in column J.

Is this possible ?. If so, can someone explain (in simple terms please) how to do it.

Cheers

 

[xlbo]

how is that any different ??

It uses the same syntax and interestingly, uses the same xlNone constant. Do you make any changes to the spreadsheet before your colleague attempts to use it ?? is there any sheet / cell level protection in place by any chance ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

In case it helps: the code works perfectly in my copy of Excel97. Thanks Geoff.


Gavin

 

[Jock1970]

Hi Goeff,

Sorry - it looked different to me, but I don't have your expert eyes.

You have triumphed again - there is protection on the worksheets and when I remove this it works perfectly in Excel 97 too.

I can't understand why it works in 2003 with the protection on, but not in 97.

I'll have to use the workbook unprotected in the meantime until I can work out what element of protection is doing the damage.

Thanks for your help Goeff.

 

[xlbo]

In xl97, you may only have sheet level protection - ie the whole sheet is protected. In 2003, you can have cell level protection and other properties that may mean that you can either change certain attributes of a protected cell or that the cells being changed in this instance were not protected.
Should be pretty simple to get around - just use:

Private Sub Worksheet_Calculate()
Dim FormatRng As Range, testRng As Range

Const OSet = 8

[b]Me.Unprotect password:="passwordgoeshere"[/b]

Set FormatRng = Me.Range("B9:G298")
Set testRng = Me.Range("J9:J298")

With FormatRng
 .Interior.ColorIndex = xlNone
 For Each c In testRng
    Select Case c.Text
        Case "Text1"
            .Rows(c.Row - OSet).Interior.ColorIndex = 3
        Case "Text2"
            .Rows(c.Row - OSet).Interior.ColorIndex = 4
        ...etc etc...
        Case Else
            .Rows(c.Row - OSet).Interior.ColorIndex = xlnone
    End Select
  Next c
End With
[b]Me.Protect password:="passwordgoeshere"[/b]
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question