Range Question ??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 28 Nov 2007 15:49 GMT

HYCH

My question is:

I have a range from H2:I32, within this range i have values of 1 and 0
spread throughout,

is it possible to run a vba macro to check for a 1 and then colour the
cell red,
i know how to use the 'Pattern.Solid Colorindex and numbers, but am
having very little luck with the actual code to check this range,
would prefer to have this running as a worksheet change event

Hych

Steve

Reply to this Message

hall.jeff@gmail.com - 28 Nov 2007 16:47 GMT

I would suggest using conditional formats (formats->Conditional
formatting)

If it really needs to be vba though (and it doesn't sound like it
does... )

Public Sub colortest()
Dim wb As Workbook
Dim sht As Worksheet
Dim cell

Set wb = ThisWorkbook
Set sht = wb.Worksheets("Sheet1")

For Each cell In sht.Range("H2:I32")
If cell.Value = 1 Then
cell.Interior.ColorIndex = 1
Else
cell.Interior.ColorIndex = 0
End If
Next

End Sub

This actually changes between white and black... but you get the idea

Reply to this Message

Steve - 28 Nov 2007 17:48 GMT

On 28 Nov, 16:47, hall.j...@gmail.com wrote:

> I would suggest using conditional formats (formats->Conditional
> formatting)
[quoted text clipped - 21 lines]
>
> This actually changes between white and black... but you get the idea

thanks hal,

Would use Con From, but does have a tendency to make the filesize
larger than needed, where as i find Vba macro has less impact on file
sizes.

Thanks very much
Steve

Reply to this Message