Macros highlighting cells, some help needed

S

son_goku

New Member
Joined
Mar 14, 2011
Messages
16
Hello, I'm trying to write a macro(s) to highlight and sum up particular cells:
1) blank cells
2) cells two columns to the left from a cell containing "TRUE"
Everything should be clear on the scrshot below:

5407d5d81e.png

Here is some code I was trying to use (as two separate macros):

Code: 
[FONT=Calibri][SIZE=3]Sub MissingValues()[/SIZE][/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[FONT=Calibri][SIZE=3]'[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' MissingValues Macro[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' Finds all missing values within the range specified,[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' highlights the empty cells with blue background colour, font white and bold.[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' Also, counts missing values for every column and row.[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]'[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]'[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]Dim valuesRange As Range[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim i As Integer[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim j As Integer[/SIZE][/FONT]
<o:p></o:p>
<o:p></o:p>
[SIZE=3][FONT=Calibri]   On Error Resume Next[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.DisplayAlerts = False[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Set valuesRange = Application.InputBox _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   (Prompt:="Please select the range of values:", _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Title:="SPECIFY RANGE", Type:=8)[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]   On Error GoTo 0[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.DisplayAlerts = True[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]       If valuesRange Is Nothing Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           Exit Sub[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Else[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]           For Each Cell In valuesRange[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               If IsEmpty(Cell) = True Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               ' If Len(Trim(Active.Cell)) = 0 Then[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]                   With Selection.FormatConditions(1).Font[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                       .ThemeColor = xlThemeColorDark1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                   End With[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                   With Selection.FormatConditions(1).Interior[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                       .Color = 12611584[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                   End With[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           Exit For[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           Next Cell[/FONT][/SIZE]
 
 
[SIZE=3][FONT=Calibri]         '  For i = 0 To Rows[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]        '[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       '        For j = 0 To Cols[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      '[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     '              If active/current.cell Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    '               ' IsEmpty[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   '[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]               '       With Selection.FormatConditions(1).Font[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                '          .ThemeColor = xlThemeColorDark1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                 '     End With[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                  '    With Selection.FormatConditions(1).Interior[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                   '       .Color = 12611584[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                    '  End With[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]'[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]               '    End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              '[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]             '  Next j[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]            '[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           'Next i[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]       End If[/FONT][/SIZE]
<o:p></o:p>
<o:p></o:p>
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

and

Code: 
[FONT=Calibri][SIZE=3]Sub Outliers()[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]'[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' Finds the outliers basing on the z-value and[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' highlights the cells - the corresponding values (two columns to the left), not TRUEs[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' with yellow background colour, changing the font colour to red and bolding it.[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' Also, counts outliers for every column and per each row as well.[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]'[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]'[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]   Dim valuesRange As Range[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Set valuesRange = Application.InputBox _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   (Prompt:="Please select the range of values:", _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Title:="SPECIFY RANGE", Type:=8)[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   valuesRange.Select[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Selection.FormatConditions.Add Type:=xlTextString, String:="TRUE", _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       TextOperator:=xlContains[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   ' Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   With Selection.FormatConditions(1).Font[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .Color = -16776961[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End With[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   With Selection.FormatConditions(1).Interior[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .Color = 65535[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End With[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   ' Selection.FormatConditions(1).StopIfTrue = False[/FONT][/SIZE]
<o:p></o:p>
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]


They won't work the way I want them to though, but at least I tried ;)
Cheers for any help.

--EDIT--
Forgot to mention that the user has to select the range, and then everything is done within it (down the column first, then go to the next row)
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi,

You need to give some more details about the structure of the file.
- Is the colum sequence always the same? begins with values then "x" column and outlier check?
- should summary be always placed in the same place i.e. one row/column after data range should be free and then summary should appear?
- do you want to use conditional formatting or format those cells permanently.

I've made some changes to the first sub and here is what I came up with. (this is just the formatting part). See if it works for you:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MissingValues()<br><br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' MissingValues Macro</SPAN><br><SPAN style="color:#007F00">' Finds all missing values within the range specified,</SPAN><br><SPAN style="color:#007F00">' highlights the empty cells with blue background colour, font white and bold.</SPAN><br><SPAN style="color:#007F00">' Also, counts missing values for every column and row.</SPAN><br><SPAN style="color:#007F00">'</SPAN><br><br><br><SPAN style="color:#00007F">Dim</SPAN> valuesRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> valuesRange = Application.InputBox _<br>(Prompt:="Please select the range of values:", _<br>Title:="SPECIFY RANGE", Type:=8)<br><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">If</SPAN> valuesRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>****<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br>****<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> valuesRange<br>********<SPAN style="color:#00007F">If</SPAN> IsEmpty(cell) = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>************<SPAN style="color:#00007F">With</SPAN> cell<br>****************.Font.ThemeColor = xlThemeColorDark1<br>****************.Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>****************.Interior.Color = 12611584<br>************<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>********<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>****<SPAN style="color:#00007F">Next</SPAN> cell<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Hi, thanks for reply ;)


I've checked the macro, it works!


The structure is as follows:

Values An unimportant column IsOutlier
numbers some numbers TRUE/FALSE

- consider the above 3 columns as one unit. I've got two sets, each containing twelve of that kind of units.

- the summary should be placed one row/column after each "set". In fact, the range selected by the user should be the whole first set and then the other one, but the user may choose to select one "unit" only. One way or another, the summary should be where I mentioned. (however your - a very good one - question made me think that I could write the summary in the form: blanks/TRUEs for valuesA/valuesB [the "values" columns are what is the most important in the end] an so on. Meantime, I've checked you could do it using formula =$column$row&" = "&cell)

- and finally, I don't quite understand what do you mean by "do you want to use conditional formatting or format those cells permanently." - I thought that once you've run the macro, the formatting can't be "undone"


Again, many thanks for your help ;)
 
Upvote 0
Ignore the comments in the brackets, second "-", I gave up this idea.

Also, updated macros:

Missing values:
Code: 
 Sub MissingValues()

'
' MissingValues Macro
' Finds all missing values within the range specified,
' highlights the empty cells with blue background colour, font white and bold.
' Also, counts missing values for every column and row.
'

Dim valuesRange As Range
Dim c As Integer


On Error Resume Next
Application.DisplayAlerts = False

c = 0
Set valuesRange = Application.InputBox _
(Prompt:="Please select the range of values:", _
Title:="SPECIFY RANGE", Type:=8)

On Error GoTo 0
Application.DisplayAlerts = True

If valuesRange Is Nothing Then
    Exit Sub
Else
    For Each cell In valuesRange
        If IsEmpty(cell) = True Then
            With cell
                .Font.ThemeColor = xlThemeColorDark1
                .Font.Bold = True
                .Interior.Color = 12611584
            End With
            c = c + 1
        End If
    Next cell
End If

' valuesRange.Offset(0, 2) = c
' valuesRange.Offset(4, 0) = c

End Sub


Outliers:
Code: 
 Sub Outliers()

'
' Finds the outliers basing on the z-value and
' highlights the cells - the corresponding values (two columns to the left), not TRUEs
' with yellow background colour, changing the font colour to red and bolding it.
' Also, counts outliers for every column and per each row as well.
'

Dim valuesRange As Range
Dim c As Integer


On Error Resume Next
Application.DisplayAlerts = False
 
c = 0
Set valuesRange = Application.InputBox _
(Prompt:="Please select the range of values:", _
Title:="SPECIFY RANGE", Type:=8)

On Error GoTo 0
Application.DisplayAlerts = True
 
If valuesRange Is Nothing Then
    Exit Sub
Else
    For Each cell In valuesRange
        If (cell.Value) = "TRUE" Then
        'Selection.FormatConditions.Add Type:=xlTextString, String:="TRUE", _
        'TextOperator:=xlContains
            ' currentCell.Offset(0, -2)
            With Selection.FormatConditions(1).Font
                .Color = -16776961
            End With
            With Selection.FormatConditions(1).Interior
                .Color = 65535
            End With
            c = c + 1
        End If
    Next cell
End If

' valuesRange.Offset(0, 3) = c
' valuesRange.Offset(5, 0) = c

End Sub

I feel I'm closer to what I need, but still help would be really appreciated...
 
Upvote 0
More detailed description and information that you would need to help me out:

I’m using Excel 2007 on Windows Vista.
I don’t want to use CF and formulas, because the data set is very large and I need the macro to have the flexibility of changes (if I decide to put the average value of all the other numbers in the Values column into the blank cell).

The user runs the macro MissingValues:
  • Macro prompts to select a range of cells.
  • The selection will always be 3, or 6, or 9, and so on up to 36 columns and exactly 1000 (including labels) rows
  • There is a “three column” pattern: which will always be: Values, X, and isOutlier as in the example data.
  • The macro will loop (using outer and inner for loops maybe) down the column first and then go to the next columns and format the blank cells in the Values column.
  • Format: Blue background, bold font, White font color
  • Don't want Conditional Formatting
  • Count the blanks and put the count in a cell five rows below the selection in the Values column for every column and
  • Count the blanks and put the count in a cell two columns to the right from the selection for every row.

2nd separate macro called Outliers:
  • Want a separate macro (not combined with the above macro)
  • Same as the MissingValues macro except...
  • Format the Values column where the isOutlier column = TRUE
  • Format: Red background, yellow font color, bold font
  • Again, don't want conditional formatting
  • Count TRUEs and put the count six rows below selection in the isOutlier column (as in the example data), and
  • Count TRUEs and put the count three columns to the right from the selection for every row.
Excel Workbook
ABCDEFGHIJKLM
1
2valuesxIsOutliervaluesxIsOutliervaluesxIsOutlierblanksTRUEs
31xFALSE5xFALSE10xTRUE
42xFALSE10xTRUExFALSE
5xFALSE15xFALSE30xFALSE
64xTRUE20xFALSExFALSE
75xFALSExFALSE50xFALSE
86xFALSE30xTRUE60xFALSE
9xFALSE35xFALSE70xFALSE
108xTRUE40xTRUExFALSE
119xFALSExFALSE90xTRUE
1210xFALSE50xFALSE100xFALSE
13
14
15
16
17blanks
18TRUEs
Sheet1
Excel 2007
Excel Workbook
ABCDEFGHIJKLM
21
22valuesxIsOutliervaluesxIsOutliervaluesxIsOutlierblanksTRUEs
231xFALSE5xFALSE10xTRUE1
242xFALSE10xTRUEwhitexFALSE11
25whitexFALSE15xFALSE30xFALSE1
264xTRUE20xFALSEwhitexFALSE11
275xFALSEwhitexFALSE50xFALSE1
286xFALSE30xTRUE60xFALSE1
29whitexFALSE35xFALSE70xFALSE1
308xTRUE40xTRUEwhitexFALSE12
319xFALSEwhitexFALSE90xTRUE1
3210xFALSE50xFALSE100xFALSE
33
34
35
36
37blanks2232
38TRUEs23
Sheet1
Excel 2007
 
Upvote 0
I posted the problem here as well:
HTML: 
http://www.ozgrid.com/forum/showthread.php?t=151763
hoping to get more answers.
Still looking for anyone that can help me out, I really don't know how to make the details working...
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Solved
Using VBA instead of Conditional formatting
2
Replies
12
Views
849
Micron
M
K
  • Question Question
Variable losing set Range
Replies
8
Views
455
MARK858
MARK858
N
  • Question Question
My code is suddenly really slow
Replies
1
Views
375
RobP
R
P
  • Solved
Loop Not Looping too Next Sheet
Replies
3
Views
447
Joe4
Joe4
tezza
  • Question Question
VBA conditional order issue
Replies
6
Views
678
kevin9999
K

Forum statistics

Threads
1,224,586
Messages
6,179,718
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top