Conditional Formatting by Text and Colot

MrG

New Member
Joined
Aug 31, 2002
Messages
3
I have rows of cells, some cells with a background color or fill, some not. The fill in any case is always the same color. (lets say orange)
(Cells A1:M1 may be blank and N1:S1 may have fill. Where the cells are blank or have fill is different for every row.)
In a cell within a row, if the letter F was entered and the fill/color was blank, I want a cell at the end of the row (a column at the end of all rows)to be red.
In a cell within a row, if the letter F was entered and the fill/color was orange, I want a cell at the end of the row (a column at the end of all rows) to be green.
Thanks if you can solve this.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi there Mr G, Welcome to the Board

This will work (but only on the first F in the row)
I have assumed a range A1:M1 for the first row. Red or Green displaying in Column N

First create this UDF (in a macro module)

Function INTCOL(Range)
Application.Volatile
INTCOL = Range.Interior.ColorIndex
End Function

Next create this event macro (worksheet macro)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

Next put this formula in cell N1

=IF(INTCOL(INDIRECT(ADDRESS(ROW(),MATCH("F",A1:M1,0),4)))=-4142,1,2)
Change the font color for column N to white (or whatever is your background color)
(so the following numbers don't show)
If the first F on row 1 is in a blank cell N1 will display 1
If the first F on row 1 is in a colored cell N1 will display 2
On cell N1 use this conditional formatting
Cell value = 1 set font and background to red
Cell value = 2 set font and background to green
Select N1 and scroll the formula down

Hope this helps
regards
Derek
This message was edited by Derek on 2002-09-01 20:43
 

MrG

New Member
Joined
Aug 31, 2002
Messages
3
Thanks Derek,
Almost does it.
It works for the first column of data I matched no problem.
But when I move to the next, only red shows up for some reason.ie:
row a7:n7 is cell work and o7 is the column where I put the if and condtional formatting. I scrolled down and it works on all rows great. Then I move to the next section P7:AB7, paste from o7 etc. However, regardless if there is color/fill in any cell in the range from P7 to AB7, when I have an "F" entered, I get red in AC7. May be the way I put in the macro or something as I'm only a neophite at that and perhaps didn't enter it in the correct place?
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
Hi again MrG

The reason it does not work when you move the formula to a different column is as follows:
Match("F",P7:AB7,0) returns the position of "F" in that range. Your original range A7:P7 meant that the position of "F" was the same as its column number. So the formula uses this as the column number for the address of "F". Since the range no longer starts from the first column Match no longer gives the correct column number - because you have moved the range 15 columns to the right.
To correct this you need to add this displacement (+15) to the formula, as below.

=IF(INTCOL(INDIRECT(ADDRESS(ROW(),MATCH("F",P7:AB7,0)+15,4)))=-4142,1,2)

You will need to make a similar adjustment whenever your range does not start from column A

Hope you can follow this
regards
Derek
 

MrG

New Member
Joined
Aug 31, 2002
Messages
3
Derek, thanks very much.
With the offset, that did the trick.
Appreciate your prompt response and knowledge.
 

Forum statistics

Threads
1,144,360
Messages
5,723,903
Members
422,526
Latest member
edminty84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top