Double click to format cells of non continuous columns

[excelcraze]

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.

 

[excelcraze]

Can someone help me?

 

[Rick Rothstein]

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]

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]

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...

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]

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.

[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]

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

 

[excelcraze]

I believe there is no way to do as I mentioned in the second scenario .

 

[Rick Rothstein]

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...

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