function to colour cells

[KAH]

I want to write a function to colour a given cell based on the value of a
variables. Below if what I have so far. But I am not sure how to define the
function or how to use cells in functions. Any assistance is appreciated.

If wsResults.Cells(x, y).Value < (z) Then
Cells(x,y).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ElseIf wsResults.Cells(x,y).Value > (z) Then
Cells(x,y).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If

 

[broro183]

hi,


Try these examples for colouring cells base on selected/defined
ranges...



Code:
--------------------
Option Explicit
Sub Test()
Dim wsResults As Worksheet
Set wsResults = ActiveSheet
'example one
x = 1
y = 3
Call ColourCell(wsResults.Cells(x, y), 5)
'example two
Call ColourCell(wsResults.Range("a4: b10"), 5)
Set wsResults = Nothing
End Sub

Sub ColourCell(rng As Range, z As Long)
Dim Cll As Range
Dim CllColour As Long
Application.ScreenUpdating = False
For Each Cll In rng
With Cll
Select Case .Value
Case .Value < z
CllColour = 36
Case .Value > z
CllColour = 37
End Select
With .Interior
.ColorIndex = CllColour
.Pattern = xlSolid
End With
End With
Next Cll
Application.ScreenUpdating = True
End Sub

--------------------


hth
Rob

 

[broro183]

hi,

When reviewing my last post I noticed a potential for error if the
value is equal to z. To overcome this the Select case statement could be
modified to...


Code:
--------------------

Select Case .Value
Case .Value < z
CllColour = 36
Case .Value > z
CllColour = 37
Case .Value = z
CllColour = 40
Case Else 'eg if there is non-numeric/text string in a cell
CllColour = 50
End Select

''alternatively you could merge the "=z" line with one of the others ie
' Case .Value <= z
' Case .Value >= z

--------------------


hth
Rob