PDA

View Full Version : Need Macro Help



dhylbert
12-11-2012, 11:22 AM
I am trying to set up a macro in VBA to do the following: When a user changes a cell, I want the cell's fill color to change. I need it to work in conjunction with existing macro witch I will post below. If someone removes or adds an "X" in the columns I want to easily see that it was changed. Please Help..

Sub CFO_OC()
Dim LR As Long
Dim i As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("D" & i).Value = "Facility CFO" Then
Range("f" & i).Value = "X"
Range("j" & i).Value = "X"
Range("n" & i).Value = "X"
Range("r" & i).Value = "X"
Range("v" & i).Value = "X"
Range("z" & i).Value = "X"
Range("ad" & i).Value = "X"
Range("ah" & i).Value = "X"
Range("al" & i).Value = "X"
Range("ap" & i).Value = "X"
Range("at" & i).Value = "X"
Range("ax" & i).Value = "X"
Range("bb" & i).Value = "X"
Range("bf" & i).Value = "X"
Range("bj" & i).Value = "X"
Range("bn" & i).Value = "X"
Range("br" & i).Value = "X"
Range("bv" & i).Value = "X"
Range("bz" & i).Value = "X"
Range("cd" & i).Value = "X"
Range("ch" & i).Value = "X"
Range("cl" & i).Value = "X"
Range("cp" & i).Value = "X"
End If
Next i
End Sub

CodeNinja
12-11-2012, 11:49 AM
Use the worksheet on change event...

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = 65535
End Sub

dhylbert
12-11-2012, 12:03 PM
Can I then Identify only for certain columns. I tested and it is not exactly what I need. I want it to auto - populate only if someone either removes or adds and "x" and no other keyboard functions....thank you for your help...i am getting closer

CodeNinja
12-11-2012, 02:40 PM
Dhylbert,
Please do NOT post multiple posts with the SAME issue.

You can use a series of if/Then statements in the worksheet change and selection change events to accomplish what you want... The code below will change an interior color to yellow if an X is added, and to Red if an X is removed.


Dim bx As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "X" Then
Target.Interior.Color = 65535
End If
If Target = "" And bx = True Then
Target.Interior.Color = 255
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target = "X" Then
bx = True
Else
bx = False
End If
End Sub

demirufuk
03-30-2015, 01:09 AM
Hi CodeNinja...

I am trying to write a VBA code that allows format change for some certain range of cells from `USD` currency to a custom format `;;;` (to make it invisible) after data entry and file is saved. I am searcing for three days and got nothing.
Any suggestion or help would be appriciate.

Thanks a lot in advance
Ufuk