Macro to change color of a cell if that cell's value is <0.05

[Hughly741]

<style>@font-face { font-family: "Cambria"; }p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 10pt; font-size: 12pt; font-family: "Times New Roman"; }div.Section1 { page: Section1; }</style> I was wondering if somebody could help me with this problem. I have been trying to make a macro that will change the font color (or highlight a cell) if the value of the cell is below 0.05. I am able to do it, both for cells I individually select myself as well as cells within the same column, but I cannot get it to work if I set the selection range to be for example A:D in row 1. I thought about making many different short scripts, one for each column and each iterating through all of the rows but I am sure that there is a better way. Could anybody help me with this?

 

[Joe4]

Welcome to the Board!

Why is a macro necessary? Couldn't you just use Conditional Formatting?
Conditional Formatting can be based on values in other cells (it is not limited to just values in the cell being formatted).

 

[HalfAce]

I am sure that there is a better way

Does this have to be done with vba? Is there a reason you can't use conditional formatting?

From the menu (depending on your version of Excel...)
Format > Conditional Formatting > Cell Value Is > Less Than > 0.05 and then just format to suit.

If for some reason you still need to do this with vba, yes it can certainly be done (post back if that's the case) but this is what conditional formatting was made for.

Hope it helps.

 

[Hughly741]

Thanks for the quick reply Joe4 and HalfAce. I didn't actually know about conditional formatting until you told me about it. Thanks for that. The data that I want is on every third row and there are about 300 rows of it. The other two rows also have numbers that may be below 0.05 but I don't want them highlighted.
The idea was for each iteration to go down by three.


With my very limited knowledge of VB, I put together what I pasted below. The problem with what I have made is that it is unable to move to different columns. I can use the conditional formating but then I have to click on a plethora of cells. Doable, but I would prefer to have something a bit faster.

Dim Row As Integer
Row = 115
For i = 1 To 120


rangeString = "E" & Row & ":E" & Row
Range(rangeString).Select
If Selection.Value < 0.05 Then Selection.Font.Bold = True
If Selection.Value < 0.05 Then Selection.Interior.ColorIndex = 36

Row = Row + 3
Next i
End Sub

 

[Joe4]

I can use the conditional formating but then I have to click on a plethora of cells.

No you don't. You should be able to apply the formatting on your whole range at once.

Let's say that you want to apply it to every third row starting at 1 (so rows 1,4,7,10,...). You could highlight the whole range and apply a conditional formatting formula that starts like this:

=and(mod(row(),3)=1,your formula)

What the first part does is check the current row number and divide by three. If the remainder is 1, then that condition is met (in an "AND" formula, all conditions must be met for it to return true).

 

[HalfAce]

The problem with what I have made is that it is unable to move to different columns.

Yes, with hard coded range references you'll have a problem when changing their locations. (Your code will still be pointing to the old location.)

You can however, highlight your entire range and give it a defined range name. (Named Range) This way you can refer to the named range in your code and it will know where that range is no matter where it may get moved to.

Now, as for your example posted code; I can't really tell (for sure) what you're wanting it to do.
Right now it's being told (Row = 115) "assign the variable 'Row' to mean row 115.
(Should not use 'Row' as a variable. Should use Rw or MyRow or something like that.)

Then it's saying (For i = 1 To 120) "start at row 1 and loop through until row 120"

Then (rangeString = "E" & Row & ":E" & Row) is assigning the variable 'rangeString' to refer to cell E115. If you want it to refer to that cell's VALUE, then you would write that like:
rangeString = Range("E" & Rw).Value
Or - if you want rangeString to refer to that cell as a range, then you would write it like:
Set rangeString = Range("E" & Rw)

The rest just selects the range (we don't need to do that) and checks the value to format the bold font & background color, and progress on to the next 'Row', which is being told to be 'whatever I am plus 3'.

So... as it stands now, (were all the syntax correct) you're telling it to go through 120 iterations of checking cell E115. (and then starting on the second iteration, you're changing the variable 'Row' to become row 118.)

Am I right in guessing you want to loop through column E, from rows 1 to 120, checking every 3rd cell in the column for a value of <0.05?

If that's so then perhaps something along the lines of:

Dim i As Long
For i = 1 To 120 Step 3
  With Cells(i, "E")
    If .Value < 0.05 Then .Font.Bold = True: .Interior.ColorIndex = 36
  End With
Next i

Does this help at all or am I guessing completely wrong?

 

[Hughly741]

Thanks Joe4, I think that will do what I want.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
HalfAce:<o></o>
Yah, your comment does help. That is a much more concise way of referencing a cell. <o></o>
When I run that macro, it begins at row 115 then goes down to row 475 applying the test and format to every third row all in column E. My problem with it is that it is limited to column E. <o></o>
<o></o>
The rangestring bit was remnants from when I tried using the script to select multiple cells and have it perform the logical test on all of them. I had (rangeString = "E" & Row & ":BF" & Row) When that didn’t work, I just changed the BF to E<o></o>
<o> </o>
Although Joe4's comment solved my problem, I am now separately curious if it is possible to make a script (HalfAce’s, for example) iterate through different columns. As it is, it will go through 360 rows, but only one column. I can't just add 1 to the column as it is a letter. Is there some way to convert it into a number or move to another column (adjacent or otherwise)?

Again, your knowledge and help are greatly appreciated.

 

[HalfAce]

A couple things here to answer your questions.
1) If you wanted to loop through a range of more than one column, you could set a range in the code and have it loop through the range. (ie.)
For Each Rng in Range("E115:G475")
do your stuff here
Next Rng

2) When it comes to specifying a column in vba, we can do it using the column letter or the column number. (ie.)
(Specify a variable to mean a column)
MyColumn = Range("E:E")
- OR -
MyColumn = 5 (column E being the fifth column when counting from the left)
This way you can change the column of interest in mid-routine by using:
MyColumn = MyColumn + 1
That will switch it to look from column E to column F.
(By the same token, MyColumn = MyColumn + 4 will switch MyColumn to go from meaning column E to meaning column I.)

And... when you're writing the code and have to know the number(s) of any specific column(s), you can go to any columns on the sheet and enter the formula: =Column() and it will return the number value of that column. (Just makes it easier than counting them out for every column reference you want to make in the code.)

Does that help with what you're wondering?

 

[Hughly741]

Hi,
Joe4:
I tried your suggestion, but I must be doing something wrong with my formula.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>
=AND(MOD(ROW(),3)=1,Value > 0.05)
</o>=AND(MOD(ROW(),3)=1,Cell.Value > 0.05)
=AND(MOD(ROW(),3)=115,Cell.Value > 0.05)

I was able to get it to work if I only put
=AND(MOD(ROW(),3)=1)
as condition one and
Cell Value is less than 0.05



Just for the practice, I also made a maccro that did the same:

Sub HighlightSig()

Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("C114:BF281")

For Each row In rng.Rows
For Each cell In row.Cells

If cell.row() Mod 3 = 0 Then

If cell.Value < 0.05 Then cell.Font.Bold = True: cell.Interior.ColorIndex = 36
End If

Next cell
Next row

End Sub

I really appreciate both of your help.