API tools faq
paste
Advertisement
letseehowmuch

VBA

letseehowmuch
Jul 9th, 2025 (edited)
79
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VBScript 4.71 KB | None | 0 0
raw download clone embed print report
  1. Sub ApplyCustomFormatting()
  2.     Dim ws As Worksheet
  3.     Set ws = ThisWorkbook.Sheets("Design") ' Change as needed
  4.    Dim lastRow As Long
  5.     lastRow = ws.Cells(ws.Rows.Count, "Y").End(xlUp).Row
  6.     Dim i As Long
  7.     Dim cell As Range
  8.     Dim fCrosshatch As String, fRed As String, fYellow As String, fGreen As String
  9.  
  10.     For i = 14 To 300 ' Adjust start row as needed
  11.        Select Case ws.Cells(i, "Y").Value
  12.             Case "Checkbox", "Dropdown", "Input"
  13.                 Set cell = ws.Cells(i, "Z")
  14.                 cell.Style = "Normal Entry"
  15.  
  16.                 ' Data Validation
  17.                With cell.Validation
  18.                     Select Case ws.Cells(i, "Y").Value
  19.                         Case "Checkbox"
  20.                             .Delete
  21.                             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
  22.                                  Operator:=xlBetween, Formula1:="=TorF"
  23.                             .IgnoreBlank = True
  24.                             .InCellDropdown = False
  25.                             .InputMessage = "Use Checkbox"
  26.                             .ErrorMessage = "Use Checkbox"
  27.                             .ShowInput = True
  28.                             .ShowError = True
  29.                             cell.Style = "Normal Entry Checkbox"
  30.                         Case "Dropdown"
  31.                             .Delete
  32.                             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
  33.                                  Operator:=xlBetween, Formula1:="=INDIRECT(X" & i & ")"
  34.                             .IgnoreBlank = True
  35.                             .InCellDropdown = True
  36.                             .ErrorMessage = "Please use the dropdown"
  37.                             .ShowError = True
  38.                     End Select
  39.                 End With
  40.  
  41.                 ' Cell Style
  42.                
  43.  
  44.                 ' Clear existing formatting
  45.                cell.FormatConditions.Delete
  46.  
  47.                 ' Add left border without removing others
  48.                With cell.Borders(xlEdgeLeft)
  49.                     .LineStyle = xlContinuous
  50.                     .Weight = xlThin
  51.                     .ColorIndex = xlAutomatic
  52.                 End With
  53.  
  54.                 ' Shared conditions
  55.                fCrosshatch = "=OR($V" & i & "=FALSE, AND($S" & i & "=FALSE, ReportType<>""Final""), AND($T" & i & "=FALSE, ReportType<>""Rough""))"
  56.                 fRed = "=OR($W" & i & "=TRUE, AND($Z" & i & "=TRUE, $V" & i & "=FALSE))"
  57.                 fYellow = "=AND($U" & i & ", NOT($Z" & i & "))"
  58.                 fGreen = "=$Z" & i & "=TRUE"
  59.  
  60.                 Select Case ws.Cells(i, "Y").Value
  61.                     Case "Input"
  62.                         fGreen = "=LEN($Z" & i & ")>0"
  63.                         fRed = "=OR($W" & i & "=TRUE, AND(LEN(TRIM($Z" & i & "))>0, $V" & i & "=FALSE))"
  64.                     Case "Dropdown"
  65.                         fYellow = "=AND($U" & i & ", OR(LEN($Z" & i & ")=0, $Z" & i & "=""Not Met""))"
  66.                         fGreen = "=LEN($Z" & i & ")>0"
  67.                         fRed = "=OR($W" & i & "=TRUE, AND(LEN(TRIM($Z" & i & "))>0, $V" & i & "=FALSE))"
  68.                 End Select
  69.  
  70.                 ' Apply Conditional Formatting
  71.                ' 1. Crosshatch (NO stop if true)
  72.                With cell.FormatConditions.Add(Type:=xlExpression, Formula1:=fCrosshatch)
  73.                     .Interior.Pattern = xlPatternChecker
  74.                     .Interior.PatternColorIndex = xlAutomatic
  75.                     .StopIfTrue = False
  76.                 End With
  77.  
  78.                 ' 2. RED (shared across all types)
  79.                With cell.FormatConditions.Add(Type:=xlExpression, Formula1:=fRed)
  80.                     if ws.Cells(i, "Y").Value = "Checkbox" Then
  81.                         .Font.Color = RGB(255, 0, 0)
  82.                     End If
  83.                     .Interior.Color = RGB(255, 0, 0)
  84.                     .StopIfTrue = True
  85.                 End With
  86.  
  87.                 ' 3. YELLOW
  88.                With cell.FormatConditions.Add(Type:=xlExpression, Formula1:=fYellow)
  89.                     if ws.Cells(i, "Y").Value = "Checkbox" Then
  90.                         .Font.Color = RGB(255, 255, 153)
  91.                     End If
  92.                     .Interior.Color = RGB(255, 255, 153)
  93.                     .StopIfTrue = True
  94.                 End With
  95.  
  96.                 ' 4. GREEN
  97.                With cell.FormatConditions.Add(Type:=xlExpression, Formula1:=fGreen)
  98.                     if ws.Cells(i, "Y").Value = "Checkbox" Then
  99.                         .Font.Color = RGB(146, 208, 80)
  100.                     End If
  101.                     .Interior.Color = RGB(146, 208, 80)
  102.                     .StopIfTrue = True
  103.                 End With
  104.         End Select
  105.     Next i
  106.  
  107.     MsgBox "Formatting applied to all rows in column Y."
  108. End Sub
  109.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!