2 questions

[Newbee via OfficeKB.com]

Good Day,

I'm still kind of new at writing macro's and had two quick questions. Is
there a way to find in a selected data in Column AI if there is a negative
integer to highlight, well more like fill row with a color. The follow up
question would be could i do the following, with a selection of data could
you do this...If Column AI has #DIV/0! and Column AN has a 0 hide that row,
but it has to have #DIV/0! (AI) and a 0 (AN). Thank you very much and i do
apprectiate any help i can get with this.

 

[Guest]

Here's a macro to hide rows based on the columns/values you listed. You
would start this macro with your cursor in the bottom most row or below.

Sub Hiding_Rows_Macro()

Do While ActiveCell.Row > 1
If Cells(ActiveCell.Row, 35).Text = "#DIV/0!" And Cells(ActiveCell.Row,
40).Value = 0 Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
ActiveCell.Offset(-1, 0).Select
Loop

End Sub

Regarding your first question, you can use Conditional Formatting to make a
cell a different color if negative (you would say <0 to indicate negative).
On the menu, choose Format > Conditional Formatting.

Hope this helps,

Keith

 

[Don Guillett]

question 2

Sub hideif()
mc = "al"
On Error Resume Next
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If IsError(Cells(i, mc)) Then
If Cells(i, mc).Value = CVErr(xlErrDiv0) _
And Cells(i, mc).Offset(, 2) = 0 Then
'MsgBox i
Rows(i).Hidden = True
End If
End If
Next i
End Sub

 

[Don Guillett]

question1
Sub colofif()
mc = 6
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
Rows(i).Interior.ColorIndex = 0
If Cells(i, mc) < 0 Then Rows(i).Interior.ColorIndex = 36
Next
End Sub