VB conditional formatting problem

[Pauljj]

I have written this code below but no matter whether C4 = V3 or not it always formats it to red (colorindex 3) Can anyone see a glowing mistake of mine please

Private Sub Worksheet_Change(ByVal Target As Range)
Set DayOne = Range("C6:D50")
For Each Cell In DayOne

If Range("C4") <> Range("V3") Then
Cell.Interior.ColorIndex = 1

End If

If Range("C4") = Range("V3") Then
Cell.Interior.ColorIndex = 3

End If

Next

End Sub

 

[Sektor]

If C4 and V3 are equal, then you will ALWAYS get red color. Change logic.

 

[VoG]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
For i = 6 To 50
    If Range("D" & i).Value <> Range("V" & i - 1).Value Then
        Range("C" & i).Resize(, 2).Interior.ColorIndex = 1
    Else
        Range("C" & i).Resize(, 2).Interior.ColorIndex = 3
    End If
Next i
End Sub

 

[Pauljj]

That's right though. If C4 = V3 then I want it to be red, otherwise I want it to be another colour

 

[Rick Rothstein]

I have written this code below but no matter whether C4 = V3 or not it always formats it to red (colorindex 3) Can anyone see a glowing mistake of mine please

Your code works fine for me. What kind of values are in C4 and V3 and how do those values get into the cells (formula, typed, copied from somewhere, etc.)? By the way, your code can be simplified quite a bit...

Private Sub Worksheet_Change(ByVal Target As Range)
  Range("C6:D50").Interior.ColorIndex = 1 - 2 * (Range("C4").Value = Range("V3").Value)
End Sub

However, this is not the code I would use as it runs every time a change is made on the worksheet. You should make the code run only when a change is made in C4 or V3; however, to write the code to do that, we need to know whether C4 and V3 have constant values or values resulting from formulas in them.

 

[Pauljj]

Oddly enough VoG that has a rather strange effect, some cells colour red and some colour black whether the 2 ranges equal each other or not....its a mixture now

 

[VoG]

I was guessing at what you wanted and obviously guessed wrong.

 

[Pauljj]

Hello Rick

C3 is a date, formatted as a number, it is coming from a formula simply saying "=C4"


V3 is a date as well formatted as a number, it is coming from a formula saying

=INDEX(C5:P5,MATCH(T3,T3,0))

 

[Rick Rothstein]

C3 is a date, formatted as a number, it is coming from a formula simply saying "=C4"

Why are you telling us about C3 (your test is for C4 against V3)?

V3 is a date as well formatted as a number, it is coming from a formula saying

=INDEX(C5:P5,MATCH(T3,T3,0))

Two things to check:

1) Are both cells that you are checking returning pure dates... don't look at the displayed (formatted) values, look at the underlying values... are there time values in either one?

2) Are both cells values actually numbers or are they perhaps actually text values (the number format won't change a text value into a numeric value)?

In the code you posted originally, change your test to this and see if it makes a difference...

If CLng(Range("C4")) <> CLng(Range("V3")) Then

 

[Pauljj]

Hello Rick, yes, both dates are pure dates, there is no time element in the result and both are also purely numeric...I attach my spreadsheet example

Tailormade Day Plan 2.xls
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2
3	EnterDate:	14/06/2011																ChooseDayofWeek:	Wednesday		40708
4		40708		40709		40710		40711		40712		40713		40714
5	UploadfromRoster	Tuesday		Wednesday		Thursday		Friday		Saturday		Sunday		Monday				Name	Duty1	Time	to
6																		AkshadaChandorkar	Alpha	07:00	13:00
7	KarenKnapton																	AlainFrimigacci
8	MikeGwynne																	AlesZelenka
Data

To explain what I need it to do is, to highlight anything in grey which is not the date selected in T3

So in the drop down if you select Wednesday, all the other days of the week (columns C:O) appear in grey (was using red as an example thats all)


The date in C4 is exactly what is in C5 (which comes from C3) , the reason for having the date in C5 as well as C4 is only because C5 and D5 are merged and I initially thought this may have caused the problem

The drop down in T3 is text only, so I did a look up to return a date in V3 as you can see


I changed my code as you suggested but still made no difference

Private Sub Worksheet_Change(ByVal Target As Range)
Set DayOne = Range("C6:D50")
For Each Cell In DayOne

If CLng(Range("C4"))<> CLng(Range("V3")) Then
Cell.Interior.ColorIndex = 1

End If

If CLng(Range("C4")) = CLng(Range("V3")) Then
Cell.Interior.ColorIndex = 3

End If

Next

End Sub