Conditional Color Formatting for more than 3 conditions

[Magnus575]

I am trying to find a way to do conditional color formatting for more than 3 conditions using Excel 97. I tried to write a function that would take in a cell reference and a color index number and the function would change the color of the cell referenced with the color that corresponds with the passed index number. For some reason the function did not work but a subprocedure written exactly the same did work. I need this to be a user defined worksheet function however. Does anyone know of a way to accomplish this?

[sen_edp]

take a look

http://www.mrexcel.com/board/viewtop...orum=2&start=0

It Might help

[Magnus575]

I have no problem writing my own sub procedure for changing cell back ground colors. I'm running into a problem getting a user-defined function for changing a cells background color to work. I really need the change to be transparent to the user so that when their input values trigger a color change it is automatic, that is they don't have to go and run a subprocedure to take care of the comparisons and color changes. I could use event-handling procedures in my application but a simple user-defined color change function would be a lot easier and cleaner. Plus it is now bothering me that I can't get the function to work. As an experiment try a user-defined function such as the following:

function colorchange()
activesheet.range("a1").interior.colorindex = 36
End Function

Considering I typed in the code right here, when I call this function from any cell in the worksheet it should change the background color of cell A1 to the corresponding index color for 36 which I think is a yellowish color. This however does not work. If I write a similar sub procedure:

sub colorchange()
activesheet.range("a1").interior.colorindex = 36
End Sub

When the sub procedure is executed the background color of cell A1 is indeed changed like expected. Does excel not allow you to change a cells background color through a function?

[sen_edp]

hello Magnus

You have to understand that a user defined function cannot make changes in the cells or in the worksheet.
A function cannot change the color of a cell,
the only thing can do is to calculate and return a value , a result.
That's why everyone choose vba to do the conditional formatting with more than three criteria .

Hope that helps

[Jack in the UK]

This will do as you wish your need to edit the code a bit, to your needs but its all there.

Hope this is OK
Let me know if you need help the code is all commented to help you edit.


Sub Rows_Color() ' Written by Jack in the UK 21 May 2002
On Error GoTo JACKintheUK
' Jack set error handle
Sheets("B W").Select
' Jack selets the required sheet
' Jack change as needed
Range("A1:R500").Select
'Jack select the required range
Selection.Interior.ColorIndex = xlNne
' Jack removes any colours in the above range
Range("A1").Select
' Jack hey i choose a cell
Application.ScreenUpdating = False
'Jack removes screen updates FASTER code this way
JACK = 1
' Jack sets Jack = 1
'>>>>>>>>>>>>>>>>>>>>
' Jack says Red you would like for a YES????
With Sheets("B W") 'Jack in the UK
For YES = 1 To WorksheetFunction.CountIf(.Columns("B:B"), "YES")
Set RYESFoundCell = .Columns("B:B").Find(What:="YES", _
After:=.Cells(JACK, 2))
RYESFoundCell.EntireRow.Interior.ColorIndex = 4
'1= black 2= white 3= red 4=lime green 5= blue 6= yellow
JACK = RYESFoundCell.Row
Next YES
End With
'>>>>>>>>>>>>>>>>>>>>>
' Jack says Red you would like for a YES????
With Sheets("B W") 'Jack in the UK
For N = 1 To WorksheetFunction.CountIf(.Columns("B:B"), "N")
Set RNFoundCell = .Columns("B:B").Find(What:="N", _
After:=.Cells(JACK, 2))
RYESFoundCell.EntireRow.Interior.ColorIndex = 3
JACK = RNFoundCell.Row
Next N
End With
'>>>>>>>>>>>>>>>>>>>>>
' Jack says Red you would like for a YES????
With Sheets("B W") 'Jack in the UK
For Maybe = 1 To WorksheetFunction.CountIf(.Columns("B:B"), "Maybe")
Set RMaybeFoundCell = .Columns("B:B").Find(What:="Maybe", _
After:=.Cells(JACK, 2))
RMaybeFoundCell.EntireRow.Interior.ColorIndex = 6
JACK = RMaybeFoundCell.Row
Next Maybe
End With
'>>>>>>>>>>>>>>>>>>>>>

Columns("C:IV").Select
Selection.Interior.ColorIndex = xlNne
' Jack rips the colour from the right (removes it)
' set as required
' (Dont really need (left in case) dirty who cares?

Application.Goto Reference:="R1C1"
'Jack returns to a cell in this case A1
'(can be any i guess)
Application.ScreenUpdating = True
' Jack sets updtes to live
JACKintheUK:
' Jack compleate error handle
End Sub

MARKER conditional format
MARKER 3 formats

[Magnus575]

Thanks guys, I didn't realize that a function couldn't change a cells property. It makes sense though. Thanks for the ideas in the vba code example as well.