Double click to format cells of non continuous columns

excelcraze

Board Regular
Joined
Sep 10, 2012
Messages
81
Hello,

I have to format a cell when double click a cell based on the data in it. The problem is that the formatting criteria are not same for all cells. Suppose, when you double click a cell that contain OK in columns A, B, D, F,H then that cell color change to red however if the cell you click contain OK that if that is in column C,E or G the cell color change to blue. Can you tell me how can I do this.

Thanks in advance.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
Hello,

I have to format a cell when double click a cell based on the data in it. The problem is that the formatting criteria are not same for all cells. Suppose, when you double click a cell that contain OK in columns A, B, D, F,H then that cell color change to red however if the cell you click contain OK that if that is in column C,E or G the cell color change to blue. Can you tell me how can I do this.
Is OK the only word you want to do this with or are there other words also? If other words, what are they or where can they be found? Will the color scheme be the same for the other words as for the word OK?
 

excelcraze

Board Regular
Joined
Sep 10, 2012
Messages
81
Yes, OK is the only word. But if OK are in column A, B, D, F,H then when you double click that cell it needs to be changed to red color. If OK is in column C,E or G then that needs to be changed to blue. Meaning to say this double click functionality is for particular columns not for entire worksheet. I hope you understand me.

Thanks.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Yes, OK is the only word. But if OK are in column A, B, D, F,H then when you double click that cell it needs to be changed to red color. If OK is in column C,E or G then that needs to be changed to blue. Meaning to say this double click functionality is for particular columns not for entire worksheet. I hope you understand me.
See if this event code does what you want...

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If UCase(Target.Value) = "OK" Then
    If Not Intersect(Target, Range("A:A,B:B,D:D,F:F,H:H")) Is Nothing Then
      Target.Interior.Color = vbRed
    ElseIf Not Intersect(Target, Range("C:C,E:E,G:G")) Is Nothing Then
      Target.Interior.Color = vbBlue
    End If
  Else
    Target.Interior.ColorIndex = xlColorIndexNone
  End If
  Cancel = True
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

excelcraze

Board Regular
Joined
Sep 10, 2012
Messages
81
Thank you very much. It did exactly what I needed.



Here is another scenario and I have written the following code and of course I know it's not the perfect coding (am just a beginner). What I want is when user click a cell that contains nothing, it's color should be changed to green, add date and time and computername. If he double click that cell again all contents in the cell should be cleared and color should be none (but this function should limit to some columns only which I couldn't do at all, say Column B, C, E,F,H,I) in the same sheet there are other columns (say, Column A,D,G,J), If user double click that cell it's color should be changed to green, add date and time and computername with the data already in it. If the user double click it again all data should be cleared except the original data which was already there. and the color should be none.
If required, in these columns the data always have 9 characters (Alpha numeric)


Thank you very much.

Code:
[FONT=trebuchet ms][COLOR=#000066]Private Sub Worksheet_BeforeDoubleClick(<wbr>ByVal Target As Range, Cancel As Boolean)[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]Dim Comp As String[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms]ActiveWorkbook.Unprotect "1234"[/FONT][/COLOR]

[FONT=trebuchet ms][COLOR=#000066]Sheets("sheet1").Unprotect "1234"[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms]If Selection.Value = "" Then[/FONT][/COLOR]

[COLOR=#000066][FONT=trebuchet ms]        If Selection.Interior.ColorIndex = 10 Then[/FONT][/COLOR]

[FONT=trebuchet ms][COLOR=#000066]             With Selection.Interior[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .Pattern = xlNone[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .TintAndShade = 0[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .PatternTintAndShade = 0[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                Selection.Font.ColorIndex = xlAutomatic[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]             End With[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms]            Selection.ClearContents[/FONT][/COLOR]

[FONT=trebuchet ms][COLOR=#000066]        Else[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms]            With Selection.Interior[/FONT][/COLOR]

[FONT=trebuchet ms][COLOR=#000066]                .Pattern = xlSolid[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .PatternColorIndex = xlAutomatic[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .ColorIndex = 10[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]            End With[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]            Comp = Environ("computername")[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]             With Selection[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms] .Font.ColorIndex = 10[/FONT][/COLOR]
[FONT=trebuchet ms][COLOR=#000066]                .Value = Date & "|" & Time & "|" & Comp[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms]              End With[/FONT][/COLOR]

[COLOR=#000066][FONT=trebuchet ms]        End If[/FONT][/COLOR]

[COLOR=#000066][FONT=trebuchet ms]Else[/FONT][/COLOR]

[COLOR=#000066][FONT=trebuchet ms]        If Selection.Interior.ColorIndex = 10 Then[/FONT][/COLOR]

[FONT=trebuchet ms][COLOR=#000066]            With Selection.Interior[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .Pattern = xlNone[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]            End With[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]            With Selection.Font[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .ColorIndex = xlAutomatic[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]            End With[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms]        Else[/FONT][/COLOR]

[COLOR=#000066][FONT=trebuchet ms]            With Selection.Interior[/FONT][/COLOR]

[FONT=trebuchet ms][COLOR=#000066]                .Pattern = xlSolid[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .PatternColorIndex = xlAutomatic[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]                .ColorIndex = 10[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]            End With[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]            Comp = Environ("computername")[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]            v = Selection.Value[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]             With Selection[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms] .Font.ColorIndex = 2[/FONT][/COLOR]
[FONT=trebuchet ms][COLOR=#000066]                .Value = v & "|" & Date & "|" & Time & "|" & Comp[/COLOR][/FONT]
[COLOR=#000066][FONT=trebuchet ms]            End With[/FONT][/COLOR]

[COLOR=#000066][FONT=trebuchet ms]        End If[/FONT][/COLOR]

[FONT=trebuchet ms][COLOR=#000066]End If[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]
[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]Cancel = True[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]
[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]ActiveWorkbook.Protect "1234"[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]Sheets("sheet1").Protect "1234"[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]
[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]End Sub[/COLOR][/FONT]
[FONT=trebuchet ms][COLOR=#000066]
[/COLOR][/FONT]
 

excelcraze

Board Regular
Joined
Sep 10, 2012
Messages
81

ADVERTISEMENT

Can you help me, I need it for my office. Thanks
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,858
Office Version
  1. 2010
Platform
  1. Windows
I believe there is no way to do as I mentioned in the second scenario . :( :( :(

I am not 100% sure, but I think this BeforeDoubleClick event code will do what you want...

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("B:C,E:F,H:I")) Is Nothing Then
    If Len(Target.Value) Then
      Target.ClearContents
      Target.Interior.ColorIndex = xlColorIndexNone
    Else
      Target.Value = Date & " | " & Time & " | " & Environ("ComputerName")
      Target.Interior.ColorIndex = 10
    End If
    Cancel = True
  ElseIf Not Intersect(Target, Range("A:A,D:D,G:G,J:J")) Is Nothing Then
    If Target.Interior.ColorIndex = 10 Then
      Target.Interior.ColorIndex = xlColorIndexNone
      Target.Value = Left(Target.Value, InStrRev(Target.Value, vbLf) - 1)
      Cancel = True
    Else
      Target.Interior.ColorIndex = 10
      Target.Value = Target.Value & vbLf & Date & " | " & Time & " | " & Environ("ComputerName")
      Cancel = True
    End If
  End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,151
Messages
5,623,058
Members
415,949
Latest member
mcrandall99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top