What is the formula I need when using conditional formatting?
I have a number of symbols in column A. While doing a loop at times one of the symbols in column A will appear at J27. When that happens I would like the background in matching symbol in column A to turn green. Thanks for any help on this one
I have everything else working just fine. Just coloring the background.
Thanks
Max
Max,
you are doing what? Sorry, I don't quite catch what you mean by "symbols". Is it text? And are you planning to use formulas or VBA?
Puzzled,
Isabella
IBihy Thanks for the reply. What I am refering to is stock symbols in column A.
Lets say there are 300 stock symbols in column A. When a stock symbol appears in
J27 I need the cell with what is in J27 to look over in column A find the same symbol
and color the cell green. NOTE: I want the cell with the green color to remain green
when the new stock symbol appears in J27 I need to look over in column A again and find the new symbol and color that cell green also. At the end there might be ten cells
with green backgrounds. Thanks for any help on this one. I have been able to get the program to work with only one cell at a time.
Max
select range in col A to be conditional formatted. use the following formula:
[vba]
=COUNTIF(D1:Z500,A1)>0
[/vba]
exp:
look for col A value in Range(D1:Z500), if at least one match is found, conditional format column A.
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
Mancubus Thanks for the help. I have been busy working on some other things is
the reason I have not posted. I will rate this thread.
I do have one other project i need some help with. When using the Loop in Excel I have
the results in J27. The result is a stock symbol. The symbol stays there for three seconds because of the timer. This gives me time to copy the symbol to paper before it goes away and the next symbol appears. Is there a way to copy the symbol in J27 to J28? When the next symbol appears in J27 it will be copied to J29 etc untill the Loop
has run its course? This would help a lot. I could just sit back and sip on some coffeeLOL.
Thanks
Max
The first range should be absolute, rows at least, preferably rows and columnsOriginally Posted by mancubus
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
How does the value get into J27?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
The symbol in J27 using IF AND command. If the symbol passes all the screens
I refer to D1 which is copied from all the symbols in column A. When running the
screens sometimes there is no symbol in J27 due to the fact the symbol did not pass
all the screens. What I am after is IF a symbol appears in J27 I want that symbol put
in J28. When the nest symbol appears in J27 I want it put in J29 untill the LOOP has finished. Can this be done? If so THANKS as that will save me the trouble of copying
the symbols as they come up. I love to watch the LOOP go thru all the symbols and the other command I use is VLookup to retreive all the data from other ss I have.
Max
A simplified example, The code you need is something like this I think.
[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$E$7" Then
If Target <> "" Then Target.Insert xlDown
Target.Offset(1) = Target
End If
Application.EnableEvents = True
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
