change colorindex 26 to 35 in a spreadsheet

Posted on 2006-05-10
Last Modified: 2013-12-25
I want an efficient macro that looks an entire sheet for cells with colorindex26 and changes the colors of these cells to color index 35

Thank you.
Question by:Aiysha
    LVL 35

    Accepted Solution

    Hi Aiysha,

    Unfortunately there is no efficient way to search for colorindex, you do have to loop through the cells to determine the colorindex.

    However, there is a workaround, that stays with the individual workbooks.  Probably not what you're looking for here, but is the only way to do it other than looping.  You can change what colorindex 26 refers to for that workbook:
     YourWorkbook.Colors(26) = YourWorkbook.Colors(35)

    LVL 1

    Assisted Solution


    i think u should try CONDITIONAL FORMATTING
    u will get what u want .. i hope
    if it works le me know


    Author Comment


    Even if you do conditional formatting, you would still have to visit each and every cell in the spreadsheet. I agree with mvidas there is no efficient way of doing this task.

    LVL 1

    Expert Comment


    even if u  use conditional formating
    the operation will be cell-by-cell
    but it will be one line formula that will do ur job  else
    u will have to write loops for that ,
    if i am wrong correct me
    as i don't have that much experience in EXCEL vba


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now