Conditional formatting in macro

[Zak]

Hi,

I have the following conditional formatting requirements to be put in one
macro -

1) If no info is entered in a cell or cell in blank then colour grey (in all
sheet)
2) If cell value is less than cell C6 in column C then colour Red
3) If cell value is more than cell C6 in column C then colour Yellow
3) Current month column to be highlighted

are you able to help? thanks a lot.

 

[Mike H]

Zak,

You need to define this a bit more because to check every cell in a
worksheet (>16million) and set the background according to the contents is
going to take a very long time.

Mike

 

[Zak]

Hi,

Its not a big spreadsheet atol..would this make it easier now?

Just want to say if something is blank then turn grey.. the whole sheet
would probably only have like 40blanks.

The current month column - there is only one of these in the whole sheet.

And what if i had names instead of cell refs? for example, if "AB" less than
"CD" in row 3 then colour red etc..?

I thought it would be easily done, like with conditional formatting... maybe
not!

So, can this not be done?

thanks.

 

[Mike H]

Zak,

I've assumed your range is A1 - B100 , try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:B100")) Is Nothing Then
If Target.Value > Range("C6") Then Target.Interior.ColorIndex = 6
If Target.Value < Range("C6") Then Target.Interior.ColorIndex = 3
Set myrange = Range("A1:B100")
For Each c In myrange
If IsEmpty(c) Then
c.Interior.ColorIndex = 15
End If
Next
End If
End Sub

Mike

 

[Zak]

Hi,

I inserted this code into a module but when i go to find it to play it i
cant seem to find it, its no where to be seen! is there something in the code
that would make it do that or am i doing something wrong? i have tried
another macro and that works fine..

i put it in exactly like you said. Also, am i able to get another condition
put in? If cell contains curent month then put a green border around whole
column? and in the code i said if is something is >,< than C6 but in future
this cell is likely to move. am i able to say a word (whatever is in the
cell) instead of C6?

thanks a lot.

 

[Mike H]

Its worksheet code so right click the sheet tab, view code and paste it in
there

 

[Zak]

I did this but how do i play it then? when i go back to worksheet view i
click on macros and play and i cant see it there, is there a different way to
play worksheet macros. sorry i dont know!

 

[Nigel]

The code is triggered by a worksheet change event - you do not need to run
it manually.

Each 'change' on the specific worksheet causes the code to run and setup the
color coding you require within the range specified


--

Regards,
Nigel
(e-mail address removed)

 

[Zak]

Hi.

I am now trying to trigger some change but it doesnt seem to do anyting.
most the sheet in the specified range is blank so it should be grey but it
isnt! i am deliberately trying or evoke the conditions but again nothing
happens.

Does this have to be a worksheet function because the spreadsheet this is to
be used on would be created from another report via a macro and it will
change weekly. This would mean the code will no longer stay as it is
something that is re-created. I do not want to insert this code each time,
can i not make it a part of the normal macro?

Also, can i use words instead of cell refs? as cell refs are likely to
change over time.

I also wanted to know how i can insert a condition to make current month
column blue bordered.

thanks.