VBA change font color if existing cell value changes

[roniaelm]

Hi,

I have found a macro on the net and edited so that if an exisiting
cell value is changed the font will change to red.
However, I am trying to amend this code so that it refers to an entire
column and not just a cell. I am very new to VB and have not found a
way. Can someone help me amend this code or know a better way of doing
this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = ("$A$2") Then
Range("A2").Font.Color = RGB(255, 0, 0)
End If
End Sub

 

[Rick Rothstein]

You didn't tells, so assuming the column you want to change is Column A...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
Range("A2").EntireColumn.Font.Color = RGB(255, 0, 0)
End If
End Sub

 

[roniaelm]

You didn't tells, so assuming the column you want to change is Column A....

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then
        Range("A2").EntireColumn.Font.Color = RGB(255, 0, 0)
    End If
End Sub

Hi Rick,

My column is A.
However, I am not sure if this adjustment is doing what it needs to.
My range is in column A (not just A2) and if any cell is changed in A
then I need the cell to change the font colour.
Is there a way to do this?

Thanks!

 

[Paolo Sardi]

The Range("A2").EntireColumn properties returns a range referring to the
whole column containing A2, that means olumn A

 

[roniaelm]

The Range("A2").EntireColumn properties returns a range referring to the
whole column containing A2, that means olumn A

Hi Paoloa,

I don't think I have made myself clear enough.
What I want is for any cell in Column A that has a change made to it's
value then for that cell to change the font colour, i.e. if the change
is made in A3 then for A3 to change colour, if it was made in A4 then
for A4 to change colour etc.
I don't want the entire column to change based on cell A2 changing
value. I don't want the entire column changing colour at any point.

I hope that makes more sense and that it is possible to get a VBA to
do this job.

Thanks for your help!

 

[Noob McKnownowt]

Hi Paolo,

try:

sub ChangeColour()

dim iLastRow as integer
dim Cell as range
dim Rng as Range

iLastRow = Sheets1.Range("A" & Rows.Count).End(xlUp).Row

set Rng = sheet1.range("A1:A" & iLastRow)

for each cell in rng
if cell.value <> "What ever number should be here" then
cell.font.color = RGB(255,0,0)
end if
next cell

end sub

but you should be a little more specific are you looking for number(s) in
cell(s) in col A of the sum of the entire col? any way hope the code i have
provided helps you out.

The Noob.

 

[roniaelm]

Hi Paolo,

try:

sub ChangeColour()

dim iLastRow as integer
dim Cell as range
dim Rng as Range

iLastRow = Sheets1.Range("A" & Rows.Count).End(xlUp).Row

set Rng = sheet1.range("A1:A" &  iLastRow)

for each cell in rng
      if cell.value <> "What ever number should be here" then
             cell.font.color = RGB(255,0,0)
      end if
next cell

end sub

but you should be a little more specific are you looking for number(s) in
cell(s) in col A of the sum of the entire col? any way hope the code i have
provided helps you out.  

The Noob.

Hi Noob,

thanks for your help.
But the values in the cells can be text or numbers and they are not
specific to a particular word or number. I just want excel to detect
when the original value has changed in a cell and change the font
colour to red.
I am not sure if your suggested code does this at the moment

 

[egun]

Insert this code into the worksheet in which you want to change the font
color. Select the column you want to monitor, and then try making some
changes to cells in that column.

HTH,

Eric

Option Explicit

'
' This routine will change the font color of any
' cell in the chose column on the active worksheet
' to a different color if that cell is changed.
'
Private Sub Worksheet_Change(ByVal Target As Range)
Dim theCell As Range
Dim theArea As Range
Dim theColumn As Integer
'
theColumn = 1 ' Column "A" ==> set to desired column
'
For Each theArea In Target.Areas ' Can have multiple areas selected...
For Each theCell In theArea.Cells
If (theCell.Column = theColumn) Then ' Only cells in chosen
column...
theCell.Font.ColorIndex = 3 ' Set to red, or other color
End If
Next theCell
Next theArea
End Sub

 

[roniaelm]

Insert this code into the worksheet in which you want to change the font
color.  Select the column you want to monitor, and then try making some
changes to cells in that column.

HTH,

Eric

Option Explicit

'
' This routine will change the font color of any
' cell in the chose column on the active worksheet
' to a different color if that cell is changed.
'
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim theCell As Range
    Dim theArea As Range
    Dim theColumn As Integer
'
    theColumn = 1   ' Column "A" ==> set to desired column
'
    For Each theArea In Target.Areas    ' Can have multiple areasselected...
        For Each theCell In theArea.Cells
            If (theCell.Column = theColumn) Then   ' Onlycells in chosen
column...
                theCell.Font.ColorIndex = 3   ' Set to red, or other color
            End If
        Next theCell
    Next theArea
End Sub

Hi Eric,

Thanks sooooo much! You are a life saver!
The code works and does exactly what I want it to do!

Thanks!!!!

 

[Wisdomandlaughter]

Eric,

I am searching for a code that will do exactly what the code you posted will
do but for a range of columns rather than one column. Currently the columns
being used are A:N. Is it possible to tweak the code you provided to do
this?

Thank you much.

Judy