Change font colour

[Altec101]

Hello,

Is there a way I can change the font colour to red in the entire row
based on a value in a cell using an IF statment without using the
Conditional Formatting ?

Thanks,

 

[Jim Thomlinson]

Nope... Formulas return values. They do not manipulte the format of cells.
Why not use conditional formats???

 

[Altec101]

So if I have a date in let say cell B2 and I have a if statment in a
macro that looks at the date in that cell and compares it with the
currenty system date, if the dates match I want it to colour the row
red if not skip that row and check the next one.

This can not be done ??????

I'm trying to make it as easy as posiable for some else can just run a
macro with out havn't to setup conditional formatting.

 

[Bob Phillips]

Sub ColourRed()
If ActiveCell.Value = Date Then
ActiveCell.EntireRow.Interior.ColorIndex = 3
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Jim Thomlinson]

Sorry... I misunderstood your question. So if I have it straight you want a
macro to look though a group of cells and colour the entire row red if the
cell value equals the system date... This is untested but it should be close.

Sub MakeRed()
dim rngToSearch as range
dim rng as range

set rngtosearch = range("B2", cells(rows.count, "B").end(xlUp))
for each rng in rngToSearch
if rng.value = date(now()) then rng.entirerow.font.colorindex = 3
next rng
end Sub

 

[Leith Ross]

Hello Altec101,

Place this code in a standard VBA module. It will compare the active
cell's contents to the System Date. If there is a match, it change the
Row's Font color to red.


Code:
--------------------
Sub MakeRowFontRed()

If ActiveCell.Value = Date Then
With ActiveCell.EntireRow
.Select
.Font.Color = RGB(255, 0, 0)
End With
End If

End Sub

 

[Altec101]

Thanks guys for the help.

How can I get it to loop through a range of cells say in column F.
I would like to to check each cell in column F one after the other(the
number of cells can be different each time) and be able to compare the
value in the cell which is a date to the systems date, if the value is
greater then or equal to the systems date colour the cell font red if
not do nothing.

 

[Bob Phillips]

Sub ColourRed()
Dim i As Long
For i = 1 To Cells(Rows.Count, "F").End(xlUp)
If Cells(i, "F").Value = Date Then
Cells(i,"F").Font.ColorIndex = 3
End If
Next i
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Altec101]

Thanks,

One more question.

The value in each cell in column F is stored in a variable called
cellDate i then us the DateAdd function to add 4 days to the cellDate
and store the new date in a variable called returnDate.
I then want to compare the returnDate variable with the systems date
using the Date function and if the returnDate is >= to the systems
date, colour the row red, if not skip the row and check the next row.
The loop statment should check the cell in each row and compare it
against the system date.

 

[Bob Phillips]

Don't see why you need the variables

Sub ColourRed()
Dim i As Long
For i = 1 To Cells(Rows.Count, "F").End(xlUp)
If Cells(i, "F").Value + 4 >= Date Then
Cells(i, "F").Font.ColorIndex = 3
End If
Next i
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Altec101]

It's returning an error for me, Type mismatch (Error 13)

 

[Bob Phillips]

You must have text there. Try this

Sub ColourRed()
Dim i As Long
For i = 1 To Cells(Rows.Count, "F").End(xlUp).Row
If IsNumeric(Cells(i, "F").Value) Then
If Cells(i, "F").Value + 4 >= Date Then
Cells(i, "F").Font.ColorIndex = 3
End If
End If
Next i
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Altec101]

Not working, does not do anything.
In that cell there is a date and time value.

 

[Bob Phillips]

Well not much else I can think of, works fine for me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Altec101]

What type of data do you have entered in the cell ?

 

[Altec101]

This here code does work, but I don't need to add 4 days onto the date
in the cell, i need to get the date 4 days from the cells date and then
check that date against the system date.
So, if cell F2 had a date of 5/30/2006 enter, I need to know the date 4
dates from that date, which is 6/4/2006.

That is why i was storing the date in a variable called ReturnDate, it
was the date 4 days from the cells value date.


Sub ColourRed()
Dim i As Long
For i = 1 To Cells(Rows.Count, "F").End(xlUp)
If Cells(i, "F").Value + 4 >= Date Then
Cells(i, "F").Font.ColorIndex = 3
End If
Next i
End Sub