How to apply the macro for multiple cells in a column

[karthik537]

Hi Friends,

I have 2 columns (Column A and Column B), in which Column A is a dropdown list(FR, Query, Others). In Column A, for a particular cell(for eg: A2), if I select either "FR" or "Query", the respecive row(For Eg:B2) in Column B should be disabled. I have the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ColLtr As String, RowNo As Integer
    RowNo = ActiveCell.Row
    ColLtr = "B"
    If Target.Address = "$A$" & RowNo Then
        If Target.Value = "FR" Or Target.Value = "Query" Then
            Range(ColLtr & RowNo).Interior.Color = RGB(192, 192, 192)
            ActiveSheet.Cells.Locked = False
            Range(ColLtr & RowNo).Locked = True
            ActiveSheet.Protect
        Else
            ActiveSheet.Unprotect
            Range(ColLtr & RowNo).Interior.ColorIndex = xlNone
 
        End If
    End If
 
End Sub

But the above vb script is running only for one time. Let us say for first time if I select cell A2 and have chosen the value "FR" from list, the cell B2 is disabled but if I select another cell in Column A (For eg: A3) in which I selected "FR" in drop down list, I am getting "Run Time Error 1004"...( but the B3 cell is not being disabled in Column B)

Please help me..

 

[Peter_SSs]

Welcome to the MrExcel board!

Your problem is that when you try to change the second cell, your sheet is protected and you cannot change the cell colour on the protected sheet unless you have allowed 'Format Cell" during the protection.

I also think that if that part was fixed, you may be unlocking cells that you want to remain locked. I'm not sure about that but see if this does what you want.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> ColLtr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, RowNo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    RowNo = ActiveCell.Row<br>    ColLtr = "B"<br>    <SPAN style="color:#00007F">If</SPAN> Target.Address = "$A$" & RowNo <SPAN style="color:#00007F">Then</SPAN><br>        ActiveSheet.Unprotect<br>        <SPAN style="color:#00007F">If</SPAN> Target.Value = "FR" <SPAN style="color:#00007F">Or</SPAN> Target.Value = "Query" <SPAN style="color:#00007F">Then</SPAN><br>            Range(ColLtr & RowNo).Interior.Color = RGB(192, 192, 192)<br>            Range(ColLtr & RowNo).Locked = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            Range(ColLtr & RowNo).Interior.ColorIndex = xlNone<br>            Range(ColLtr & RowNo).Locked = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        ActiveSheet.Protect<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><br></FONT>

 

[karthik537]

Thank you very much Peter!!
You are very helpful to me.....

 

[serby]

Hello,

I also have a similar problem, I have needed to work with the functions (SIN, TAN, COS, TANH etc.) and excel has given me the values of those formulas in Radians, and i needed in Degrees, so I have made a macro (with some help from internet) which convert me the values given by those formulas from Radians to Degrees
Well the problem is that my macro do convert the value of one cell (active cell) which is ok, but sometimes I need to convert an entire column.

this is the code:

Sub Macro3()
'
' Keyboard Shortcut: Ctrl+k
'
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & "*PI()/180"
With ActiveCell.Characters(Start:=1, Length:=19).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=20, Length:=5).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic


ActiveCell.Replace What:=")*PI()/180", Replacement:="*PI()/180)", LookAt _
:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With


End Sub

thank you , and sorry for my bad english

 

[Peter_SSs]

Hello,

I also have a similar problem, I have needed to work with the functions (SIN, TAN, COS, TANH etc.) and excel has given me the values of those formulas in Radians, and i needed in Degrees, so I have made a macro (with some help from internet) which convert me the values given by those formulas from Radians to Degrees
Well the problem is that my macro do convert the value of one cell (active cell) which is ok, but sometimes I need to convert an entire column.

Welcome to the MrExcel board!

When posting code, please use indented code and use Code tags to preserve that formatting (like mine below).
It makes the code much easier to read & debug.
See my signature for how to do that.

I'm unsure whether the end result in your column is supposed to be formulas or just values and therefore what that code about formatting characters is.
In any case, see if one of these helps at all.
Test in a copy of your workbook.

I've used column B so adjust the code as desired for your column.
I've also assumed data actually starts in row 2.

1. If you have formulas in the column and want to keep them, try

Sub Convert_1()
  With Range("B2", Range("B" & Rows.Count).End(xlUp))
    .Formula = "=(" & Mid(.Cells(1).Formula, 2) & ")*PI()/180"
  End With
End Sub

2. If you just want the resultant values in the column, then try

Sub Convert_2()
  With Range("B2", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate(.Address & "*PI()/180")
  End With
End Sub

 

[serby]

thank you very much,
this is just what i needed, i have used some formating code, because i have used pieces of code from Internet
anyway your solution is much more simple
both of them are useful to me...the one which give's me formula results and the one which returns values

 

[Peter_SSs]

thank you very much,
this is just what i needed, i have used some formating code, because i have used pieces of code from Internet
anyway your solution is much more simple
both of them are useful to me...the one which give's me formula results and the one which returns values

Cheers, glad to have helped.