Change Font Color

[chaboyski]

Hi -

would there be a way to change all colors of the Fonts on the cells depending on the Color of the Font you would be selecting?

example:

cells a1 - d1 would have data. example its all red from a1 - c1. then i put a green value in d1, everything would be changing to green font. would it be possible?

Thank you!

 

[HaHoBe]

Hi,

to my knowledge (might be proven wrong here) a change of color to a cell is not triggering any event that can be monitored. You might use the Worksheet_SelectionChange-event but you would have to use Enter, CursorDirectionTab or Tab to trigger this event as the cursor should stay in the same cell after a manual change of color.

Ciao,
Holger

 

[HaHoBe]

Hi,

maybe either of the two macros which need to be started manually can help:

Sub ChangeFontColorGivenRangeFormCell()

  Const cstrColStart As String = "A"
  Const cstrColEnd As String = "D"
  
  With ActiveCell
    Range(Cells(.Row, cstrColStart), Cells(.Row, cstrColEnd)).Font.Color = .Font.Color
  End With

End Sub

Sub ChangeFontColorGivenRangeInputbox()

  Dim strMsg As String
  Dim lngReturn As Long
  Dim lngFC As Long
  
  Const cstrColStart As String = "A"
  Const cstrColEnd As String = "D"
  
  strMsg = "Please make your choice:" & vbCrLf & _
            "1 - Black" & vbCrLf & "2 - Red" & vbCrLf & _
            "3 - Blue" & vbCrLf & "4 - Green" & vbCrLf & _
            "5 - Magenta" & vbCrLf & "0 or Cancel to end"
            
another:
  lngReturn = Application.InputBox(strMsg, "Change Color Font", Type:=1)
  Select Case lngReturn
    Case 0
      Exit Sub
    Case 1
      lngFC = vbBlack
    Case 2
      lngFC = vbRed
    Case 3
      lngFC = vbBlue
    Case 4
      lngFC = vbGreen
    Case 5
      lngFC = vbMagenta
    Case Else
      If MsgBox("Not an admitted entry. Do you want to try again?", _
            vbQuestion + vbYesNo, "Retry or Cancel?") = vbYes Then
        GoTo another
      Else
        Exit Sub
      End If
  End Select
  
  With ActiveCell
    Range(Cells(.Row, cstrColStart), Cells(.Row, cstrColEnd)).Font.Color = lngFC
  End With

End Sub

Both work on the row of the highlighted active cell.

Ciao,
Holger

 

[chaboyski]

Thanks @HaHoBe ! i'll try this