SOLVED

how do i set up an IF formula

Copper Contributor

i am trying to set up a formula for my daily occurrence book.

the formula that i need is if in column's D the text is specified by Fire Alarm Activation that the text colour in columns G are in red format but only on the same row that states Fire Alarm Activation.

 

is this even possible or would i have to manualy change the text colour each time that i in put a fire alarm activation text.

 

 

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@colebaird89 Add one more Conditional Formatting rule to the ones you already have in the workbook.

 

=ISNUMBER(SEARCH("fire",D2)) and apply it to $G$2:$G$95 and set the format as you desire.

 

Demonstrated in the attached file.

@colebaird89 

You're on the right track, but you need to nest your IF statement inside a Conditional Formatting rule. But first, set your 'default' color to GREEN by selecting COL G>Font Color = GREEN.

Then, WITH YOUR CURSOR IN G1,
1. Launch the Conditional Formatting wizard
    a. hotkeys = ALT+O,D, or
    b. go to the Ribbon Home>Conditional Formatting>New Rule
2. Select Rule Type "Use a Formula to determine which cells to format"
3. In the "Format values where this formula is true:" formula bar, enter the following:

 

 

=IF(LEFT($D2,4)="Fire",TRUE,FALSE)

 

 

Then click "Format" and choose Color = Red
Click OK, OK,
In the "Applies to" field, enter =$G:$G
Click APPLY, OK
Copy cell G1,
Select COL G,
and lastly Paste Special > Paste > Click "Formats" > OK
    (hotkey = ALT+E,S,T > Ok)
You're Done.
Hope this helps.
/C

This worked exactly how I wanted thank you so much I’ve been working on this for over a week and you sorted it for me thank you x
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@colebaird89 Add one more Conditional Formatting rule to the ones you already have in the workbook.

 

=ISNUMBER(SEARCH("fire",D2)) and apply it to $G$2:$G$95 and set the format as you desire.

 

Demonstrated in the attached file.

View solution in original post