coloring rows “if” true

5 pts.
Tags:
Conditional expressions
Conditional formatting
Microsoft Excel
Microsoft Excel 2003
I have a profit & loss spreadsheet that I use to input expected expenses and then log the invoices when they are paid and I always highlight the row when I input the invoice number and invoice amount. Is there a way I can use conditional formatting or a function to automatically color the row if there is anything in a the invoice amount cell to indicate that the invoice was paid?

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are using Excel you have the conditional format where you can define 3 rules to change the visual format of the data when the conditions are satisfied.
Example:
In the AMOUNT columns you define that:

  • if cell value is greater than 0 it will be colored green
  • if cell value is lower than 0 it will be colored red
  • if cell value is equal to 0 it will be underlined and colored blue

Here is a file example.

I hope this helps.

The conditional formating can be found in the format (menu)->conditional formatting.

However since you’ve used the exact combination (“conditional format”), I’m assuming you’re already are familiar with it and your question was actually – “How do I format an entire line according to a cells value?”.

This would require you to add a short script. One way to open the built in vba environment is to right click on your sheets tab at the bottom and chose “view code”. Then In the text area change “General” to “Worksheet” and chose Change in the other (that’s the event on which you’d like the code to be activated).
Here’s an example of the code you could add –

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Range 'We first declare a variable
For Each i In Target 'Now for every field in the changed area
'- we check if it's Coulmn A (1) and if the new value is possitive,
'if it is we change the color of the _ENTIRE ROW_ to horrid yellow!=)
If (i.Column = 1 And i.Value > 0) Then Rows(i.Row).Interior.ColorIndex = 6
Next i
End Sub

If you’re interested in exploring more complex visual changes, I’d recommend starting recording a macro, doing them, stopping it and going back to code view, only this time choosing modules in the tree and your macro name to see how these changes may be expressed in native vba language.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Darksaber
    Is there a way to adjust this macro to act on a range of rows (say 1 to 100) instead of just the selected row? Ive been working with it for a bit, but I'm new to macros in excel and cant quite seem to get it to work.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following