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]

You may only have 3 conditions within CF, plus the default condition. This may apply up to 4 fill colours. Any more than that will require code. You will not be able to use numeric formats as you have text rather than numbers.

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

 

[Jock1970]

Hi Goeff, thanks for the reply.

I used CF for the first 3 criteria and it works perfectly but obviously is no use as 3+1 is the maximum and I need 4+1.

Re. "You will not be able to use numeric formats as you have text rather than numbers" - I could convert the text in my Column J to a numbers (1 to 5) in say Column K by using IF formula - would this help ?

If not, am I asking the impossible ?

 

[AnotherHiggins]

Am I asking the impossible?

It is not impossible, but it will require a macro (AKA Code AKA VBA). These are the responses you have likely seen in other posts.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

If you use numbers, and one of those numbers is negative and 1 is 0 then you may have up to 6 conditions without VBA

3 conditional formats
1 format for +ve nos.
1 format for -ve nos.
1 format for 0

You can use the custom format to create a format like:

[black]0;[red]-0;[blue]0

This will show black as default for positive nos., red as default for negative nos and blue for zeroes.

then use CF to create a CF for 2, 3 and 4

you can therefore show different formats for results:
-1,0,1,2,3,4

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

 

[AnotherHiggins]

Very clever, Geoff! I hadn't thought of that.

Unfortunately it won't work in this case....

One of the columns in the front worksheet is a lookup from the other sheets .... 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.

(Emphasis mine)

To control the formats of cells other than the one containing those numbers, you would still have to use either Conditional Formatting (max of 4 different formats) or VBA.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

John - absolutely correct. Didn't quite read the question carefully enough. In this particular circumstance, VBA code will be needed to effect the 5 formats as they are not numeric

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

 

[Jock1970]

It is not impossible, but it will require a macro (AKA Code AKA VBA). These are the responses you have likely seen in other posts."

As it seems to be impossible by simpler means, could someone help me along teh VBA route ? As I said in my first post, I have read the earlier threads, but I couldn't grasp how to adapt the solutions given to my situation.

 

[xlbo]

ok - this is the thread that is usually referenced:
thread68-223068

the code in here is for the worksheet_CHANGE event. This is fired whenever a change is made in the worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
        Select Case oCell.Value
             Case Is = "Text1"
                 oCell.Interior.Colorindex = 3
             Case Is = "Text2"
                 oCell.Interior.Colorindex = 4
             Case Is = "Text3"
                 oCell.Interior.Colorindex = 5
             Case Is = "Text4"
                 oCell.Interior.Colorindex = 7
             Case Is "Text5"
                 oCell.Interior.Colorindex = 8
             Case Else
                 oCell.Interior.ColorIndex = xlNone
         End Select
    Next oCell
End Sub

'Target' references the cell that was changed and the Select Case statement parses the text found within it.

This code shoulf be copied and pasted into the WorkSHEET module. To get to it, right click on the worksheet and choose "View Code" - then just paste the code above in there. Amend Text1 - Text5 to be the text you want to test for and set appropriate color indexes....

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

 

[Jock1970]

Thanks Geoff, that's a step forward but it's still not exactly what I need.
The code above changes the fill coloour of the cell with the text in it (column J in my case). I want the cells in columns B to G to fill dependant on the value in J. What changes are required to the code to achieve this ?
Also, as column J is a look-up, when the value changes due to a change in the worksheet it looks to, the formatting doesn't happen. I have to drag the formula down over the cells again to 'refresh' the data and trigger the format change. How do I get around this ?
Thanks, in anticipation,

 

[xlbo]

Apologies - just didn't click that it was a different cell / value that would be making the change happen.

Coupla questions if I may.

1: What is the link between the formulae in column J and its precedents (ie the cells it points at) - 1 to 1 or 1 to many ?
2: Will all the formulae in col J need checking each time a value is changed ?
3: Are all your formulae in Col J in a continuous list with no gaps ?
4: where does your data start in the sheet (inc headers) - row 1...2...???

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

 

[Jock1970]

Hi Goeff,

In answer to your questions :-

1. The formula in column J is "=VLOOKUP(B9,'C - Resurfacing'!$B$7:$H$23,7,FALSE)" so is a direct look-up to a single cell in another worksheet. A 1 to 1.

2. No, the formula is permanent. The resultant value changes autiomatically as the donor cell changes in the other worksheet.

3. No. The formula above continues for a number of rows then there is a gap of 5 rows, then the formula continues again but looks at a different donor worksheet. This continues so on and so forth as the summary page collects progress data from 9 other worksheets. The gaps are purely for titles / appearance as the summary worksheet is printed off as a quarterly report.

4. Sort of answered this above, but just to clarify, my data in Column J start in Row 9 and then stops/starts all the way down the sheet. The Rows required for this exercise are 9-25, 31-32, 34-45, 51-52, 54-74, 80-85, 87-109, 115-119, 121-175, 181-182, 184-223, 229-231, 233-261, 267-268, 274-275, 278-281, 284-287, 290-298.
Having said that, I can see no problem with any formatting code on Rows 9 to 298 continuously as the gaps will not contain any data that will affect the formatting.

I hope the above is understandable,

Regards, and thankyou for your help so far,

 

[xlbo]

ok - looks like we need to go for a broad sweep approach as there is no easy way of identifying dependancies for triggering the change. As such, we will use the CALCULATE event and redo all formats each time:

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

Const OSet = 8

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

End Sub

This should be copied and pasted into the worksheet module (you can delete the other code) as before

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

 

[Jock1970]

Goeff,

Thankyou very much - that works perfectly.

Cheers !!

 

[xlbo]

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

 

[Jock1970]

Goeff,

Sorry, but I have a problem now which hopefully you can help with.

Your solution above worked perfectly and everything does what it should now - but only on my PC. I am using Excel 2003.

I tried showing a colleague how the sheet should be used on their PC (in Excel 97) and as soon as they changed a value that updated Column J in the summary worksheet a Microsoft Visual basic box popped up saying "Run-time error '1004' Unable to set the ColorIndex property of the Interior class".
When I clicked on 'Debug' it then opened a visual basic window with the line ".Interior.ColorIndex = xlNone" highlighted in yellow.

Any ideas ??

 

[xlbo]

xlNone may not be a recognised constant in xl97 - try replacing xlNone with -4142 so:

.Interior.ColorIndex = -4142

as that is what xlNone evaluates to

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

 

[Jock1970]

Hi again Goeff,

It still works perfectly in mine, and still comes up with the same error in 97 I'm afraid.

Any more ideas ?

 

[xlbo]

pretty certain it is the constant.

Does it always error on the same line ??
Try commenting out that line (use a ' in the VBE to do this) - does it error on any other lines ?

alternatively.....

Try recording a macro on your colleagues computer which sets a cell to a colour and then back to no fill. Observe the recorded code and see where it differes from what you have got

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

 

[Jock1970]

Goeff,

I tried a ' at the start of the ".Interior.ColorIndex = -4142" line. I still get an error but it's the line 2 down from before that's now highlighted. It says

Case "Not Started"
.Rows(c.Row - OSet).Interior.ColorIndex = 2

It seems strange that it did not seem to find any problem with the line above (which is identical except with text "Works Cancelled")

I then tried your other suggestion of recording a new macro in 97 to change fill colour of 1 cell then remove the fill. the macro looks like :-

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/04/06 by John Jackson
'

'
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Range("A1").Select
Selection.Interior.ColorIndex = xlNone
End Sub

Which look's quite different (to me at least) from what we have put in 2003.

Can you see what's wrong from that ?

Regards,