EXCEL宏帮助 [英] EXCEL Macro Help

问题描述

Good Afternoon Everyone，

我正在尝试创建一个允许我在Excel中搜索E列的宏电子表格中包含12个字符的字符串列表，然后更改字体颜色并填充这些单元格的背景。 

我尝试了一些宏写作，这是我的第一天，我准备把电脑扔出窗外了！

Sub PushBackFormatting（）

'有条件地格式化值

    

 栏目（5）。选择

    If Range（"E5"）。End（xlDown）.Select and Range.Value =" 09X260"，Or" 09X241"，Or" 09X297"，Or" 12X267"，Or" 07X223"，Or， " 09X327"或者，" 12X242"，或" 10X434"，或" 10X439"，或" 10X438"，或" 10X437"，或" 10X374"，或" 10x243"
然后

     使用Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

   结束与$
   使用Selection.Font

        .Color = -16711681

        .TintAndShade = 0

   结束与$
   结束如果

结束次要

解决方案

< blockquote>

DRO，

re：  "它是我的第一天"
$


实际上有13套文字。

'---

Sub PushBackFormattingR1（）

  Dim arrSize As Variant

  Dim rngCol As Excel.Range

  Dim rngLast As Excel。范围

  Dim rCell As Excel.Range

  Dim N As Long

 

arrSize =数组（"09X260"，"09X241"，"09X297"，"12X267"，"07X223"，"09X327"，_

         " 12X242"，" 10X434"，" 10X439"，" 10X438"，" 10X437"，" 10X374"，" 10x243"）

设置rngLast = ActiveSheet.Cells（ActiveSheet.Rows.Count，5）.End（xlUp）

设置rngCol = ActiveSheet.Range（ActiveSheet.Cells（1,5），rngLast）.Cells

每个rCell in rngCol

 对于N = LBound（arrSize）到UBound（arrSize）

    如果rCell.Value = arrSize（N）则为
      &NBSP; rCell.Interior.Color = vbRed

      &NBSP; rCell.Font.Color = vbBlue

     &NBSP;&NBSP;退出为
    结束如果

 下一个

下一个

结束子

'---

在我看来，要困难得多从互联网上学习VBA而不是买书。

任何John Walkenbach"Power Programming"书很好;版本在开始时并不重要。

旧货只需几美元或以1美分的价格出售+一个虚增的运费。



'---

Jim Cone

https://goo.gl/IUQUN2 中的ins和工作簿

Good Afternoon Everyone,

I am trying to create a macro that will allow me to search column E in my excel spreadsheet for a list of 12 characters strings, then change the font color and fill background of these cells. 

I have tried some Macro writing, it's my first day and I'm ready to throw the computer out of the window!

Sub PushBackFormatting()
'conditionally formatting the Values
    
  Columns(5).Select
   If Range("E5").End(xlDown).Select and Range.Value="09X260", Or"09X241", Or"09X297", Or"12X267", Or "07X223",Or,"09X327" Or,"12X242",Or"10X434",Or"10X439",Or"10X438",Or"10X437",Or"10X374",Or"10x243" Then
     With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .Color = -16711681
        .TintAndShade = 0
    End With
    End If
End Sub

解决方案

DRO,
re:  "Its my first day"

Actually there are 13 sets of text.
'---
Sub PushBackFormattingR1()
 Dim arrSize As Variant
 Dim rngCol As Excel.Range
 Dim rngLast As Excel.Range
 Dim rCell As Excel.Range
 Dim N As Long
 
arrSize = Array("09X260", "09X241", "09X297", "12X267", "07X223", "09X327", _
         "12X242", "10X434", "10X439", "10X438", "10X437", "10X374", "10x243")
Set rngLast = ActiveSheet.Cells(ActiveSheet.Rows.Count, 5).End(xlUp)
Set rngCol = ActiveSheet.Range(ActiveSheet.Cells(1, 5), rngLast).Cells
For Each rCell In rngCol
  For N = LBound(arrSize) To UBound(arrSize)
     If rCell.Value = arrSize(N) Then
        rCell.Interior.Color = vbRed
        rCell.Font.Color = vbBlue
        Exit For
     End If
  Next
Next
End Sub
'---
In my opinion, it is much more difficult to learn VBA from the internet vs. buying a book.
Any John Walkenbach "Power Programming" book is good; version doesn't really matter when starting out.
Used ones are just a few dollars or are sold for 1 cent + an inflated cost of shipping.

'---
Jim Cone
https://goo.gl/IUQUN2

这篇关于EXCEL宏帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！