changing the color of various cells (not with conditional formatting)

[ivanks]

Hello all!

I am building a spreadsheet with several columns (A to AH) and in the last one (AI) I have a checkbox with different statuses (approved, rejected, etc). I want all the cells of the row be colored when I select the status approved. How can I do this?

thanks in advance

 

[SteveO59L]

Why dont you want to use conditional formatting ?

 

[ivanks]

well, as far as I know I can use conditional formatting only with the values in the cells where I apply the formatting. I mean, if I want to color A1 apply the conditional formatting with values in A1. But what I need is to color A1 according to values for example in A2

 

[SteveO59L]

Use the "Formula Is" option

 

[SteveO59L]

Alternatively, with a bit of vb

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 34 And Target.Value = "Approved" Then
Target.EntireRow.Interior.ColorIndex = 7
End If

End Sub

 

[Peter_SSs]

Hello all!

I am building a spreadsheet with several columns (A to AH) and in the last one (AI) I have a checkbox with different statuses (approved, rejected, etc). I want all the cells of the row be colored when I select the status approved. How can I do this?

thanks in advance

Welcome to the MrExcel board!

This sounds to me like you may have used Data Validation, rather than a CheckBox in column AI. (A CheckBox is a box that is either ticked or not ticked).

Assuming it is Data Validation (or perhaps a ListBox or ComboBox linked to column AI), then expanding on SteveO59L's Conditional Formatting 'Formula Is' option, try this.

1. Select A1:AIxx where xx is the last row of data.

2. Format|Conditional Formatting...|Condition 1|Formula is: =$AI1="Approved"|Format...|Patterns tab|choose colour|OK|OK

Does that do what you want?

 

[ivanks]

Thank you Steve! now I know how to use the "formula is" option