VBA conditional formatting a range

[gmccray]

Hi,
I am trying to conditionally format a range of cells depending on the content of a column that is not attached to the cells that will be conditionally formatted. The column I am using is "AQ' and if the cells are populated with a "D" then the corresponding cells in columns "A:C" should be formatted as bold red, if the cells include "N", then the formatting will be black. I can get it to work for the first cell, but it will not loop through the additional cells and do the formatting. I am including the code below. Please help!

For counter = 1 To cCount
   Range("AQ2:AQ" & cCount).Select
    If Sheets("07 vs 08").Range("AQ2").Value = "D" Then
        Sheets("07 vs 08").Range("A2:C2").Font.Color = vbRed
        Sheets("07 vs 08").Range("A2:C2").Font.Bold = True
    Else
        Sheets("07 vs 08").Range("A2:C2").Font.Color = vbBlack
        Sheets("07 vs 08").Range("A2:C2").Font.Bold = False
    End If
    Next counter

[MickG]

XL Forum_gmccray_VBA conditional formatting a range
----------------------
Hi, I'm afraid your code is not looping, because the "For" statements, variable "Counter" is not in the loop, therefore no looping takes place.
Look at your modified code, and you will see what is required, for it to work.
I've assumed that sheet "O7 vs O8" is the activesheet.

Dim cCount As Integer, Counter As Integer
cCount = 100

For Counter = 2 To cCount
   If Range("AQ" & Counter).Value = "D" Then
         Range("A" & Counter, "C" & Counter).Font.Color = vbRed
            Range("A" & Counter, "C" & Counter).Font.Bold = True
     ElseIf Range("AQ" & Counter).Value = "N" Then
        Range("A" & Counter, "C" & Counter).Font.Color = vbBlack
             Range("A" & Counter, "C" & Counter).Font.Bold = False
       End If
 Next Counter

Regards Mick

[duncanmcl]

Mike, Like your code and it works...however I'm trying to apply this logic to the form...the cells in the range on the sheet are bold and red...this data on the form is unchanged...perhaps because it was loaded 1st?...in my situation...this code is on change and I'm trying to highlite a matching value on the record to the value in a scale of values (table). example....each record could have a different value based on some total....like a...e, those values relate to a table of rates ($/) displayed on the form, a ...e and I want to highlite a matching row on the form listbox.
thanks in advance Jim

[arlu1201]

duncanmcl,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.