Thread: How to create a macro to change the text color as i advance

I manage a list for sales reps. they are on a rotation and i track who is next by changing the text color in my excel sheet. i also track the number of leads that they receive.

right now i have two macros that only change the color of the text (one for red, one for black) and i'd like to use one macro that would advance through the list and change the current cell to red, while changing the previous one back to black.
*it would also be awesome if it could also add +1 to the current count*
ex.
2 2
2 2
4 5
6 6

Any help is always appreciated

Hello tarnold,

Welcome to the Forum!

Do you want the macro to change the active cell's font color to red and then back to black when the next cell is selected? Will this happen only to certain cells, say only in one column or only to particular group of cells?

Sincerely,
Leith Ross

Hi Leith,

Yes, it would be nice to change the active cell to red, then to black once i advance.

Also, it would only be to certain cells within one column (columns are broken down by day of week, so i'm only in one column for that day)

Any advice?

Hello tarnold,

This macro will change a single cell's font to red when selected and back to black when the next cell is selected. This will apply to the entire worksheet. If this similar to what you want to do, we can refine it for only certain cell ranges.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  Static PrevCell As Range
  
    If Target.Count > 1 Then Exit Sub
    If PrevCell Is Nothing Then Set PrevCell = Target
      
      PrevCell.Characters.Font.ColorIndex = xlColorAutomatic
      Target.Characters.Font.ColorIndex = 3    'Red
      
      Set PrevCell = Target
      
End Sub

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross