VBA code to change color of text based on location of active cell?

[EssKayKay]

Hello,

I have a question related to previous posting if you please.

I am looking for way to change the color of the text in a cell in column C based on the cell that is active in column I for the corresponding row. That is, if cell I44 is the active cell, then the text in cell C44 changes to red.

I have coding I received elsewhere that does this for a different column relationship (I apologize to the author as I don’t exactly remember who gave this to me). As example, in my current code, if the active cell is M55 then the text in C55 changes to red. I’d like a similar thing to happen if the active cell is in column I.

Here is my existing code. Please note, the proposed code does not have to be the same as the current. I simply posted it as an example. If you have a different idea that’s fine –

Option Explicit
Public Old As Integer
Public Now As Integer

‘- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    If Not Intersect(target, Range("M32:M2032")) Is Nothing Then
      Now = (Split(ActiveCell(1).Address(1, 0), "$")(1))
          If (Old <> Now) Then
             UnProtect_It
             Range(Cells(Now, 3), Cells(Now, 3)).Font.Color = vbRed
             If Old Then Range(Cells(Old, 3), Cells(Old, 3)).Font.Color = False
             Range(Cells(Now, 3), Cells(Now, 3)).Font.Bold = True
             If Old Then Range(Cells(Old, 3), Cells(Old, 3)).Font.Bold = False
             Protect_It
          End If
       Old = Now
    End If

Thanks for viewing,
Steve K.

 

[Burrgogi]

I am looking for way to change the color of the text in a cell in column C based on the cell that is active in column I for the corresponding row. That is, if cell I44 is the active cell, then the text in cell C44 changes to red.

Just so I understand you clearly on this, you want the font color to change to red when the cell is merely selected or when it is active? Those are 2 very different things. It almost sounds like you're talking about the former & not the latter

.

 

[EssKayKay]

Just so I understand you clearly on this, you want the font color to change to red when the cell is merely selected or when it is active? Those are 2 very different things. It almost sounds like you're talking about the former & not the latter

.View attachment 111787

Thank you Burrgogi for your quick response. Here is an example of what I have in mind.
Cell I34 is the active cell. Then the corresponding text (date) in column C (Cell C34) changes to red.

 

[Burrgogi]

I think you are a bit confused on the terminology.

In the screenshot you provided, cell I34 is simply selected, not activated. There is a big difference between the 2 which is why I uploaded my screenshot in my previous post.
A cell can only become active when you double-click on a particular cell making the cursor active. This allowing you to edit the contents. F2 on your keyboard does the same thing.
So in essence, you are talking about selecting one of the cells in column I and then making something happen. (e.g. change the font color)

The following VBA code should do what you want. You'll need to place the code in the same worksheet that you trying to change the font color to red. The quickest method would be to right-click on the sheet name tab, then choose "View Code" from the pop-up menu that appears. From there, just copy & paste the code below.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sRange As Range
Set sRange = Range("I3:I200")
    If Selection.Count = 1 Then
        If Not Intersect(Target, sRange) Is Nothing Then
            With Selection
                If .Offset(0, -6).Font.Color = vbRed Then
                    MsgBox "The font color is already red" & vbNewLine & "There is nothing to change."
                    Else: .Offset(0, -6).Font.Color = vbRed
                End If
            End With
        End If
    End If
End Sub

One final note:

I don't know how many rows of data are in your spreadsheet. In the screenshot you provided, the last visible row of data is 36 but I'm sure you have more than that.
You can use my code as is, however if you have more than 200 rows in the "Due Date" column, you can change line # 4:

Set sRange = Range("I3:I200")

to the actual number of rows in your spreadsheet.

 

[Burrgogi]

The time limit has expired so I cannot edit my previous post. I made a slight typo in my previous post. The defined range in line #4 should really be I33, not I3.

Set sRange = Range("I33:I200")

This is because your row of data starts at line 33.

 

[EssKayKay]

Thank you very much Burrgogi. I will take a closer look at this and let you know how this turns out.

Thanks again,
Steve K.

 

[Burrgogi]

Thank you very much Burrgogi. I will take a closer look at this and let you know how this turns out.

Thanks again,
Steve K.

I have personally never written a sub routine of this type so it was a fun little challenge for me. Happy to help.

 

[Akuini]

Cell I34 is the active cell. Then the corresponding text (date) in column C (Cell C34) changes to red.

What happen if after that you select another cell (any cell), should C34 remains red or back to "automatic"?

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 9 Then
    Columns(3).Font.Color = Vbnone
    Target.Offset(, -6).Font.Color = vbRed
End If
End Sub

 

[Akuini]

What happen if after that you select another cell (any cell), should C34 remains red or back to "automatic"?

Base on your code in post #1, it looks like you want the cell color go back to "automatic".
Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static oldCell As Range

If Target.Cells.CountLarge = 1 Then
    If Not Intersect(Target, Range("I33:I10000")) Is Nothing Then 'change the range to suit
        Target.Offset(0, -6).Font.Color = vbRed
        If Not oldCell Is Nothing Then oldCell.Font.ColorIndex = 0
        Set oldCell = Target.Offset(0, -6)
    Else
        If Not oldCell Is Nothing Then
        oldCell.Font.ColorIndex = 0
        Set oldCell = Nothing
        End If
    End If
End If
End Sub