PDA

View Full Version : Solved: Condionally Formatting a Row Based on a Cell Condition



Panda
06-14-2010, 03:45 AM
Hi There,

I have a spreadsheet containing a list of part numbers in column A (see attached) and I am trying to use VBA to condionally format the whole row if either 77154696 or 77156375 is present within column A. I know that this can be done with the conditonal format function, but this is proving to be tempremental and only working on the odd occasion.

I have managed to get some code that will highlight the cell in column A containaing either of the aforementioned part numbers but can work out how to modify the code to the the row highlighted.

Does anyone have any ideas?

The code I have got so far is;


Sub Check_Range_Value()
Dim rnArea As Range
Dim rnCell As Range
Sheets("Data").Select
Set rnArea = Range("A1:I10000")
For Each rnCell In rnArea
With rnCell
If Not IsError(.Value) Then
Select Case .Value
Case "77154696"
.Interior.ColorIndex = 35
Case "77156375"
.Interior.ColorIndex = 36
End Select
End If
End With
Next
End Sub

Thanks

Bob Phillips
06-14-2010, 03:55 AM
.

Panda
06-14-2010, 04:07 AM
Thanks for the reply but when I enter the in the details you have within the conditonal formatting box other part numbers are highlighted as well. Is there a way this can be done via VBA?

Bob Phillips
06-14-2010, 04:21 AM
Look at the example I posted an tell which other part numbers are being highlighted?

Panda
06-14-2010, 04:53 AM
I have looked at your example, and you have highlighted the range to be formatted and entered the formula =--$A1=77154696 and =--$A1=77156375 into the conditional format box. But when I do this for the spreadsheet that I am working on the results are random. I have attached a copy of the results that I am getting.

Is there something else that I need to do in order for it to work?

This data changes and gets updated weekly so I thought that trying to do this via VBA would be more stable.

Thanks for your help btw

Bob Phillips
06-14-2010, 05:32 AM
You didn't apply it properly. You have applied the CF to the range A2:I452, but you used A1 in the formulae, not A2.

CF is totally stable.

Panda
06-14-2010, 05:53 AM
I see. Thank you so much for sorting this out for me much appreciated, sorry if i seemed a bit thick bit new to this Excel business.

Thanks again =:)

Bob Phillips
06-14-2010, 05:57 AM
No problem mate, but I try to steer away from VBA unless it does something more or better than Excel functionality. In this case, it doesn't IMO.