How to set up a Macro: If cell contains background color except yellow, disable "wrap text"
Category: [Excel] Demo Available
How to set up a Macro: If cell contains background color except yellow, disable "wrap text"
Requirement:
The user is working on a macro to prepare my excel 2016 worksheet for printing. The user needs excel to check all the cells in my active worksheet and change the alignment of all cells with any background color except "NONE" and "YELLOW" (RGB: 255,255,0) to NOT wrap text.
All the other cells (including the ones that don't have background color and the yellow ones) in the users worksheet the user has the "wrap text" enabled. How can the user achieve this?
Solution:
Code:
Sub impresión_PVP_desglosados()
' Macro con Modificacion ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' impresión_PVP_desglosados Macro
Dim c As Range
Dim last As Long
last = Hoja3.Cells(Application.Rows.Count, "C").End(xlUp).Row
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
ActiveSheet.Unprotect
Columns("C:C").ColumnWidth = 36.86
Columns("E:F").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = False
' Differente de Amarillo y None
' Quizas hay que ajustar la zona que quieres modificar '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For Each c In Hoja3.Range("A9:I" & last)
If Not c.Interior.Color = vbYellow Or Not c.Interior.Color = xlNone Then c.WrapText = False
Next c
Cells.EntireRow.AutoFit
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingRows:=True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Range("A1").Select
MsgBox "Por favor, controla los saltos de página antes de imprimir"
End Sub
The user has found a problem in the following:
The code works fine as it sets the cells with background color as desired. Unfortunately it also "unwraps" the text of some cells that have no background color... It seems random to me which cells with no background color have now one or the other configuration.
As you suggested I modified the range to include all rows.
Code:
Sub impresión_PVP_desglosados_modificado()
' Macro con Modificacion ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' impresión_PVP_desglosados_modificado Macro
Dim c As Range
Dim last As Long
last = Hoja3.Cells(Application.Rows.Count, "C").End(xlUp).Row
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
ActiveSheet.Unprotect
Columns("C:C").ColumnWidth = 36.86
Columns("E:F").EntireColumn.Hidden = True
Columns("G:H").EntireColumn.Hidden = False
' Differente de Amarillo y None
' Quizas hay que ajustar la zona que quieres modificar '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For Each c In Hoja3.Range("A1:I" & last)
If Not c.Interior.Color = vbYellow Or Not c.Interior.Color = xlNone Then c.WrapText = False
Next c
Cells.EntireRow.AutoFit
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingRows:=True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Range("A1").Select
MsgBox "Por favor, controla los saltos de página antes de imprimir"
End Sub
Also, can the change the "Hoja3" to "ActiveSheet"? This way if the sheet is duplicated the macro would work with the new sheet also, correct?
Carim has suggested the following: You can replace Hoja3 by ActiveSheet.
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create a macro to display names not row numbers |
| How to set up an autofill macro |
| How to use a macro to clear cells in range with condition for each row |
| How to add a password to a macro |
| How to use a macro to auto-scroll |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.