I'm playing around with FilterClick by Debra Dalgleish (www.contextures.com) to adapt it. I'm trying to set the conditional formatting of a specific range (the first row of an auto-filtered range). The function I place as the condition checks a property of the cell (if the autofilter is active for that column) and colours the cell interior. So the formatting of each cell should reference just that cell but I can't get it to do that.
With TheTopRow set as Range($A$1:$C$1), the code below formats each cell with a reference to the whole range so I get
=FilterOn($A$1:$C$1)
instead of just the single cell.
=FilterOn($A$1) or =FilterOn(A1)
For Each cell In TheTopRow
cell.FormatConditions.Delete
cell.FormatConditions.Add Type:=xlExpression, Formula1:="=FilterOn(" & cell.Address & ")"
cell.FormatConditions(1).Interior.ColorIndex = 34
Next cell
A1 should look at A1, B1 should look at B1 etc.
How can I set the conditional format for each cell in the range to reference just itself and not the whole range?
I know there are many posts concerning this, but after scouring, I couldn't find one that fit my situation. I have a total of six worksheets, I am only concerned with two worksheets.
Worksheet (functions!)
This one has a list of numbers formatted as general. (Column G)
Is actually a formula/macro that outputs a number... (didn't know if this mattered?)
Code:
=IF(ISERROR(SEARCH(".",F192)),CleanAll(F192),CleanAll(LEFT(F192,FIND(".",F192)-1)))
Worksheet (ACD!)
This one has a list of numbers formatted as text. (Column A)
Actual typed numbers.
I want to conditionally format the cells in (functions!$G) to turn green if it finds a match in (ACD!$A), and turn red if it doesn't find a match.
Thanks in advance for the help.
I am trying to set up a function that will do a conditional format a
cell, let's say C3 in column C based on the following
Match a column, say column C with a series of other columns D-F, by
looking at the text in C2, then scan through columns D-F to find a
unique match in the row 2 cells in these columns D-F. Once the unique
row has been found (in this case it would be E) then shade the original
cell C3 if E3 has a character in it (can be 1,2,or 3). Be able to copy
this conditional format formula to all the other cells in column C. If
done correctly I would end up having all the cells in column C shaded
in the same cells as the cells that have characters in them in column
E.
If have done some similar things with CountIF and SumIF but can't get
started on this combination. Any suggestions greatly appreciated. I
am still learning to work with arrays.
C D E F
1 Jim
2 I ST I SS
3
4 1 3
5 X 2
5 3
6 X
7 2
8 X
9
10
Thanks,
Bobby D
I have a spreadsheet whereby in each cell of a row there may be a formula or/& conditional statement applicable. I would like to create a macro that can perform the insert of the new row as well copy down from cell above applicable functions as well as conditional formats that maybe resident. Can this be done?
Need some assistants in creating a conditional format.
I have to worksheets one worksheet has Names and Team selected from B6:AJ38
Second sheet has names listed in B10:B42 in cells AQ10:AQ42 I looking for a conditional format that If I enter a team that matches a team on first worksheet that a certain person has already selected that the cell would automatically add a color red.
Thanks ahead
Hey all. I am having an issue with a worksheet I'm working on that I hope somebody can help.
I have a list of 31 values in separate cells. The values are three digit letter codes. Here is an example of 5 of them:
ATL
GMI
WOB
COI
PTS
What I am wanting to do is have a Conditional Format to change the color of the cell to Pale Blue if the value equals another cell that is within a range.
Sorry if I didn't make sense, but I've been up for awhile working and I'm sure you understand.
Thank you very much!
Good Afternoon,
i need help with conditional formatting, i have a list that runs to several thousand cells, i have a masterlist that i need to match the list against to highlight the ones that are not on the masterlist, the masterlist is on the same worksheet as the list, i can get it to highlight matching cells with
=match($a1,$AA$1:$AA$500,1)
can anyone help,
many thanks andy
I have a worksheet that has conditional formatting setup for a data table (color scales) and another worksheet that houses INDEX/MATCH functions to return data from the data table. Is it possible, via INDEX/MATCH or otherwise, to also include the color of the cell from the conditional formatting?
Said another way: Suppose "Sheet1" returns a value of "100" in a specific cell using INDEX/MATCH, where that value on "Sheet2" is shaded dark green because of conditional formatting. As I have it set up now, INDEX/MATCH only returns "100" w/out the formatting.
The data is product-specific, so I can't use conditional formatting on "Sheet1" as well.
I need a formula for conditional formatting to turn a cell green based on matching values of one cell on one sheet verifing that all values are the same on another sheet in a column range.
Worksheet "Booking Sht" Cell L2 is the cell to contain the conditional format.
Same worksheet Cell D10 is the cell value to match against ALL cell values in Worksheet "Compile" M5:M5004. If ALL cells match "Booking Sht" D10 then L2 will turn green.
Thanks, Odie.
match max min.xlsxI've just used the max min match functions and wanted to pull a few more values from my list before i complete.
I was hoping that there is a way to figure out what the RPM & BHP is after Max RPM & Max BHP (As shown)
in attachments)
