在Excel中编写脚本 - 突出显示像值 [英] Scripting in Excel -- Highlighting like values

问题描述

我正在尝试创建一个宏，基本上会突出显示与A列中的值相同的颜色和蓝色和白色着色之间的交替。因此，确切的值通过颜色分组在一起。

希望这是有道理的吗？在这一刻，我正在手动突出显示（但是有12000多行，这不是一个聪明的想法）。我现在还不太了解VBA，还在努力学习更多。因此基本上这个宏将检查列A的单元格x中的值与同一列中的单元格x + 1是否相同？如果是，那么它们将被突出显示为白色。现在如果单元格x + 2与x不同，但单元格x + 2和x + 3是相同的值，它们将以蓝色突出显示。我需要通过全球彩色传播

这是一个视觉效果（想像整个行为彩色）：

解决方案

这将突出显示列中的第二个单元格。颜色可能现在是您想要的颜色。

EDIT2 ：添加了testcell1的定义，第一，第二和添加的报告为工作表代码

编辑3 ：已更改为

  Sub runthis（）
 
 
'Dim row As Integer 
 
 Dim TestCell As String 
 Dim first As String 
 Dim Second As String 
 
 Dim TestCell1 As String 
 Dim lastcell As Integer 
 
表格（sheet1）。选择
 
 Dim报告为工作表
设置报表= Excel.Worksheets（Sheet1）
 
 
 lastcell =表（sheet1）。单元格（Rows.Count，A）。结束（xlUp）.row 
 
对于row = 1 to lastcell 
 TestCell =A& CInt（row）
 TestCell1 =A& （CInt（row）+ 1）
 first = Range（TestCell）.Value 
 Second = Range（TestCell1）.Value 
 
如果first = Second Then 
 Report .Cells（row，1）.Interior.ColorIndex = 3 
 
 End If 
下一行
 End Sub 
  

I am trying to create a macro that will basically highlight like values in column A the same color and alternate between a blue and white coloring. Therefore, exact values are grouped together by color.

Hope this makes sense? At this moment I am doing the highlighting manually (but there are 12000+ rows so thats not a smart idea). I am not so great with VBA yet and am still trying to learn more.

So basically this macro will check is the value in cell x of column A is the same of cell x+1 in the same column; if it is then they will be highlighted white. Now if cell x+2 is not the same value as x but cell x+2 and x+3 are the same values, they will be highlighted in the blue color. I need teh color to spread through the WHOLE ROW

Here is a visual(imagine the whole row as colored):

解决方案

This will highlight the second cell in the column. The color might now be the extact color you want though.

EDIT2: added the definition for testcell1, first,Second and Added Report as worksheet code

edit 3: changed <> to =

Sub runthis()


'Dim row As Integer

Dim TestCell As String
Dim first As String
Dim Second As String

Dim TestCell1 As String
Dim lastcell As Integer

Sheets("sheet1").Select

Dim Report As Worksheet
Set Report = Excel.Worksheets("Sheet1")


lastcell = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).row

For row = 1 To lastcell
    TestCell = "A" & CInt(row)
    TestCell1 = "A" & (CInt(row) + 1)
    first = Range(TestCell).Value
    Second = Range(TestCell1).Value

    If first = Second Then
        Report.Cells(row, 1).Interior.ColorIndex = 3

    End If
Next row
End Sub

这篇关于在Excel中编写脚本 - 突出显示像值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！