Change format of cell based on value of cell in corresponding row?

[EssKayKay]

Hello,

I’m looking for a routine to change the status (format) of a cell in column I based on the status of a corresponding cell in the same row in column M for a range “M33:M2000” (or “I33:I2000”). I want the action to happen when the value of the cell in column M > 0. If the value of the cell in column M = 0 (or blank) then do nothing. Column M is actually formatted to Date. I would run this process via a command button.

Example: If the value in M44 is 06/12/23, then change the format of cell I44 to “Locked” and the font color to Black. Normally all cells in “I33:I2000” are unlocked & blue.

Thanks for viewing,
Steve K.

 

[awoohaw]

One more issue Awoohaw,

Here's what I'm seeing when I run the revised Test routine. Again, I'm sure I'm probably missing something here.

View attachment 112496

View attachment 112497

Thank you once again,
Steve

Well, I did not test out your code, i just added the one other line that formatted the color of the text.

 

[awoohaw]

One more issue Awoohaw,

Here's what I'm seeing when I run the revised Test routine. Again, I'm sure I'm probably missing something here.

View attachment 112496

View attachment 112497

Thank you once again,
Steve

You probably need to run this code separately since you went into break/debugging mode, the screen updating and record locking may be all awry.
Also, please note that I am writing Col EYE, not Col ONE or Col ELL.

Sub ResetWorkbook()

Dim CurSheetName as string
Dim ColIAddress as string
Dim ColI as Range

Application.ScreenUpdating = False

CurSheetName = ThisWorkbook.ActiveSheet.Name

Sheets(CurSheetName).Select

Range("I2").Select
ColIAddress = Selection.CurrentRegion.Offset(1, 0).Address
Set ColI = Range(ColIAddress)

For Each Cell In ColI
    Cell.Locked = False
    Next Cell

Application.ScreenUpdating = True
End Sub

 

[EssKayKay]

Ahh Awoohaw - I think I got it. I had to do some adjusting but it looks like it's going to do what I have in mind. I will play with this more tomorrow. If I need more help, I'll be back. I'll keep you posted. . .

Once again, very much appreciated,
Steve K.