VBA Code to Color text in entire row w doubleclick

[bosco72]

I am a newbie and need a little help!

I am looking for VBA code for an excel worksheet where if I double click a cell it will turn the text in that entire row Blue, then double click again to turn Red, then finally double click to turn it back to black. It needs to work on every row.

Any help would be appreciated!
Thanks, Bosco

 

[repairman615]

Hello,

Alt F11 to open VBEditor
On the tree at left in the project window find thisworkbook, double click on that to open the workbook module.

Paste in the folowing code:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetBeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><br>    <SPAN style="color:#00007F">If</SPAN> Target.Font.Color = RGB(0, 0, 0) <SPAN style="color:#00007F">Then</SPAN><br>        Target.EntireRow.Font.Color = RGB(0, 0, 200)<br>    <SPAN style="color:#00007F">ElseIf</SPAN> Target.Font.Color = RGB(0, 0, 200) <SPAN style="color:#00007F">Then</SPAN><br>        Target.EntireRow.Font.Color = RGB(200, 0, 0)<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Target.EntireRow.Font.Color = RGB(0, 0, 0)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Close the window and test.

-Jeff

 

[Rick Rothstein]

Right click the name tab at the bottom of the worksheet you want to have this functionality and select "View Code" from the popup menu that appears, then copy/paste the following into the code window that just opened up...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim FontColor As Long
  Cancel = True
  Select Case ActiveCell.Font.ColorIndex
    Case 3: FontColor = xlColorIndexAutomatic
    Case 5: FontColor = 3
    Case Else: FontColor = 5
  End Select
  ActiveCell.EntireRow.Font.ColorIndex = FontColor
End Sub

That's it... go back to your worksheet and double click some cells with text in their rows to see it work.

 

[bosco72]

Thanks works great!!!!!! Have a great day!

 

[Rick Rothstein]

Thanks works great!!!!!! Have a great day!

I'm not sure which message you are referring to, but posting times would seem to suggest you meant repairman615's message. Just to alert you that his code works on any worksheet in the workbook... I point that out because you said you were looking for code "for an excel worksheet" and you might not want the font color changing on other sheets. I would also suggest you place this statement into his code...

Cancel = True

Doing so will stop the double click from activating the editor for the cell (also allowing you to double click the same cell multiple times if need be).

 

[bosco72]

Yes, I meant your post worked great! Repairmans I had trouble with, but thanks for the fix for his!