Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] Conditional Formatting
Hi Community !
I need help. I do not want to use "Conditional Formatting" in Excel. I need it by VBA. If I put any value in a cell of column "D", columns A, B and C must be changed to red colour. I will need it in many cases, but as I've never worked with Cond. Form. in VBA, it will help me in many ways in the future. I thank you in advance ! =) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] Conditional Formatting
copy and paste this code into the worksheet code module. To open the module
window, right click the sheet tab and click View Code in the pop up menu. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D:D")) Is Nothing Then Range("A" & Target.Row & ":C" & Target.Row) _ .Interior.ColorIndex = 3 End If End Sub "Eddie_SP" wrote in message ... Hi Community ! I need help. I do not want to use "Conditional Formatting" in Excel. I need it by VBA. If I put any value in a cell of column "D", columns A, B and C must be changed to red colour. I will need it in many cases, but as I've never worked with Cond. Form. in VBA, it will help me in many ways in the future. I thank you in advance ! =) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] Conditional Formatting
Hi JLGWhiz !
First of all, thank you very much !!! =) I changed it a little: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D:D")) Is Nothing Then Range("A" & Target.Row & ":C" & Target.Row) _ .Font.ColorIndex = 3 Else Range("A" & Target.Row & ":C" & Target.Row) _ .Font.ColorIndex = 1 End If End Sub But when I delete the value from Column D, it does not change the colour to black, only if I chande any valeu from the column on the left (A,B,C). Do you know what how to correct that ? "JLGWhiz" wrote: copy and paste this code into the worksheet code module. To open the module window, right click the sheet tab and click View Code in the pop up menu. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("D:D")) Is Nothing Then Range("A" & Target.Row & ":C" & Target.Row) _ .Interior.ColorIndex = 3 End If End Sub "Eddie_SP" wrote in message ... Hi Community ! I need help. I do not want to use "Conditional Formatting" in Excel. I need it by VBA. If I put any value in a cell of column "D", columns A, B and C must be changed to red colour. I will need it in many cases, but as I've never worked with Cond. Form. in VBA, it will help me in many ways in the future. I thank you in advance ! =) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] Conditional Formatting
The following event code will change the same row in Columns A, B and C to
red if you enter a value into Column D and will remove that color if you delete the value in Column D. It will also allow you to make changes to multiple cells at one time as well. Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range For Each R In Target If R.Column = 4 Then With R.Offset(, -3).Resize(1, 3).Interior If Len(R.Value) 0 Then .ColorIndex = 3 Else .ColorIndex = 0 End If End With End If Next End Sub To install it, right click the tab at the bottom of the worksheet you want it to apply to, select View Code from the pop up menu that appears and then copy/paste the above code into the code window that appears. -- Rick (MVP - Excel) "Eddie_SP" wrote in message ... Hi Community ! I need help. I do not want to use "Conditional Formatting" in Excel. I need it by VBA. If I put any value in a cell of column "D", columns A, B and C must be changed to red colour. I will need it in many cases, but as I've never worked with Cond. Form. in VBA, it will help me in many ways in the future. I thank you in advance ! =) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] Conditional Formatting
I see from you response to JLGWhiz that you wanted the font color to change,
not the cell interior. Here is the code to use for that... Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range For Each R In Target If R.Column = 4 Then With R.Offset(, -3).Resize(1, 3).Font If Len(R.Value) 0 Then .ColorIndex = 3 Else .ColorIndex = 0 End If End With End If Next End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... The following event code will change the same row in Columns A, B and C to red if you enter a value into Column D and will remove that color if you delete the value in Column D. It will also allow you to make changes to multiple cells at one time as well. Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range For Each R In Target If R.Column = 4 Then With R.Offset(, -3).Resize(1, 3).Interior If Len(R.Value) 0 Then .ColorIndex = 3 Else .ColorIndex = 0 End If End With End If Next End Sub To install it, right click the tab at the bottom of the worksheet you want it to apply to, select View Code from the pop up menu that appears and then copy/paste the above code into the code window that appears. -- Rick (MVP - Excel) "Eddie_SP" wrote in message ... Hi Community ! I need help. I do not want to use "Conditional Formatting" in Excel. I need it by VBA. If I put any value in a cell of column "D", columns A, B and C must be changed to red colour. I will need it in many cases, but as I've never worked with Cond. Form. in VBA, it will help me in many ways in the future. I thank you in advance ! =) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] Conditional Formatting
Wow, Rick !
Great !!! I changed it too: Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range For Each R In Target If R.Column = 4 Then With R.Offset(, -3).Resize(1, 3).Font If Len(R.Value) 0 Then .ColorIndex = 3 Else .ColorIndex = 1 End If End With End If Next End Sub Thank you !!! I'm suffering trying to rate the posts, when I click on "YES", nothing happens !!! =( It says "thank you", but as I see, it doesn't rate it... Well, THANK YOU RICK AND JLGWhiz !!!! "Rick Rothstein" wrote: The following event code will change the same row in Columns A, B and C to red if you enter a value into Column D and will remove that color if you delete the value in Column D. It will also allow you to make changes to multiple cells at one time as well. Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range For Each R In Target If R.Column = 4 Then With R.Offset(, -3).Resize(1, 3).Interior If Len(R.Value) 0 Then .ColorIndex = 3 Else .ColorIndex = 0 End If End With End If Next End Sub To install it, right click the tab at the bottom of the worksheet you want it to apply to, select View Code from the pop up menu that appears and then copy/paste the above code into the code window that appears. -- Rick (MVP - Excel) "Eddie_SP" wrote in message ... Hi Community ! I need help. I do not want to use "Conditional Formatting" in Excel. I need it by VBA. If I put any value in a cell of column "D", columns A, B and C must be changed to red colour. I will need it in many cases, but as I've never worked with Cond. Form. in VBA, it will help me in many ways in the future. I thank you in advance ! =) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
[VBA] Conditional Formatting
You are quite welcome, of course.
Don't worry about the "Yes" option for me... it is an option for those who use the web interface when answering questions, which I do not do (I use my newsreader instead). If you are interested, and if you are willing to use a ColorIndex of 0 (Excel translates 0 into xlColorIndexAutomatic, the Automatic color) instead of 1 (black), which I think is a better way to remove the red color, then we can shorten the code considerably... Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range For Each R In Target If R.Column = 4 Then R.Offset(, -3).Resize(1, 3).Font.ColorIndex = -3 * (Len(R.Value) 0) Next End Sub -- Rick (MVP - Excel) "Eddie_SP" wrote in message ... Wow, Rick ! Great !!! I changed it too: Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range For Each R In Target If R.Column = 4 Then With R.Offset(, -3).Resize(1, 3).Font If Len(R.Value) 0 Then .ColorIndex = 3 Else .ColorIndex = 1 End If End With End If Next End Sub Thank you !!! I'm suffering trying to rate the posts, when I click on "YES", nothing happens !!! =( It says "thank you", but as I see, it doesn't rate it... Well, THANK YOU RICK AND JLGWhiz !!!! "Rick Rothstein" wrote: The following event code will change the same row in Columns A, B and C to red if you enter a value into Column D and will remove that color if you delete the value in Column D. It will also allow you to make changes to multiple cells at one time as well. Private Sub Worksheet_Change(ByVal Target As Range) Dim R As Range For Each R In Target If R.Column = 4 Then With R.Offset(, -3).Resize(1, 3).Interior If Len(R.Value) 0 Then .ColorIndex = 3 Else .ColorIndex = 0 End If End With End If Next End Sub To install it, right click the tab at the bottom of the worksheet you want it to apply to, select View Code from the pop up menu that appears and then copy/paste the above code into the code window that appears. -- Rick (MVP - Excel) "Eddie_SP" wrote in message ... Hi Community ! I need help. I do not want to use "Conditional Formatting" in Excel. I need it by VBA. If I put any value in a cell of column "D", columns A, B and C must be changed to red colour. I will need it in many cases, but as I've never worked with Cond. Form. in VBA, it will help me in many ways in the future. I thank you in advance ! =) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |